This tag is specifically for PostgreSQL version 9.1
19
votes
3answers
27k views
How do I list all schemas in PostgreSQL?
When using PostgreSQL v9.1, how do I list all of the schemas using SQL?
I was expecting something along the lines of:
SELECT something FROM pg_blah;
8
votes
1answer
1k views
Database archive solutions
In continuation to a question posted by me on Is it a good idea to move high-volume and highly-accessed tables to a separate database?, am looking out for different techniques/solutions available for ...
7
votes
3answers
6k views
Optimizing queries on a range of timestamps (two columns)
I use postgresql-9.1 with ubuntu 12.04.
I need to select records inside a range of time: my table time_limits has two timestamp fields and one property integer. Indeed there are other info columns ...
7
votes
1answer
565 views
Unexpected Seq Scan when doing query against boolean with value NULL
I have a database column called auto_review where column type is boolean.
There is an index for that field, created using the ActiveRecord ORM.
CREATE INDEX index_table_on_auto_renew ON table USING ...
7
votes
1answer
892 views
Algorithm for finding the longest prefix
I have two tables.
First one is a table with prefixes
code name price
343 ek1 10
3435 nt 4
3432 ek2 2
Second is call records with phone numbers
number time
834353212 10
...
7
votes
3answers
1k views
Store a formula in a table and use the formula in a function
I have a PostgreSQL 9.1 database where part of it handles agent commissions. Each agent has his/her own formula of calculation how much commission they get. I have a function to generate the amount of ...
7
votes
2answers
2k views
How to do incremental/differential backup every hour in Postgres 9.1?
Trying to do an hourly hot incremental backup of a single postgres server.
I have the following setup in postgresql.conf:
max_wal_senders=2
wal_level=archive
archive_mode=on
archive_command='copy ...
7
votes
2answers
665 views
Convert units of measurement
Looking to calculate the most suitable unit of measurement for a list of substances where the substances are given in differing (but compatible) unit volumes.
Unit Conversion Table
The unit ...
6
votes
3answers
475 views
Casting issue when calling a function with composite type parameter
I have a function in PostgreSQL 9.1 called fun_test. It has a composite type as input parameter and I keep getting a casting error when I call it.
what could be the issue?
CREATE OR REPLACE FUNCTION ...
5
votes
2answers
1k views
Postgresql 9 speeding up indexed inserts (JPA)
I have an application which generates a lot of data which needs to be inserted quickly (something around 13million records). I use JPA 2.0/Hibernate with Postgres 9.1, and I managed to achieve quite a ...
5
votes
3answers
1k views
How to pass a table type with an array field to a function in postgresql
i have a table called book
CREATE TABLE book
(
id smallint NOT NULL DEFAULT 0,
bname text,
btype text,
bprices numeric(11,2)[],
CONSTRAINT key PRIMARY KEY (id )
)
and a ...
5
votes
2answers
154 views
PostgreSQL: Query DB for rows containing integer[] > or < than specified value
I have a table which stores historical temperatures for the past 5 days stored in an integer[] column based on city. The question is: How can I query for entries which have a value great than or less ...
5
votes
2answers
13k views
Granting access to all tables for a user
I'm a new to Postgres and trying to migrate our MySQL databases over. In MySQL I can grant SELECT, UPDATE, INSERT, DELETE privileges on a low privileged user and enable those grants to apply to all ...
5
votes
1answer
2k views
Moving postgresql data to different drive
I am using AWS as my cloud environment. I installed PostgreSQL on the same drive as my root instance volume. I have attached and mounted the second drive to my instance. Now I want to move all my ...
5
votes
1answer
56 views
How to see amount of reusable space for a table
I had a huge table(about 300mil rows). After massive DELETE and VACCUUM table files have stopped to grow. That's exactly what I expected. But is there any way to see how much space became reusable?
5
votes
1answer
612 views
PostgreSQL 9.1 streaming replication problem: replica fails to use an index properly
We use PostgreSQL 9.1.7 on Ubuntu Linux 12.04 on a master server and PostgreSQL 9.1.7 on FreeBSD 9.0-RELEASE on a replica server. The replica and master servers return different results on the same ...
5
votes
3answers
1k views
Maximum number of databases for single instance of PostgreSQL 9
Developing a multicustomer application we plan to use a different database for each customer.
But it could be more than 1000 customers (applications).
Will PostgreSQL handle it without any problems?
...
4
votes
3answers
17k views
How do I insert a row which contains a foreign key?
Using PostgreSQL v9.1. I have the following tables:
CREATE TABLE foo
(
id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
type VARCHAR(60) NOT NULL UNIQUE
);
CREATE TABLE bar
(
id BIGSERIAL ...
4
votes
4answers
7k views
How to list all views in SQL in PostgreSQL?
How do I list all views for a database using an SQL command in PostgreSQL?
I would like something similar to output of the psql \dv command, but preferably just a list of view names. e.g.,
SELECT ...
4
votes
2answers
1k views
Index max row size error
Is there a upper bound for an array column?
I am getting this error when inserting into the array field -
PG::Error: ERROR: index row size 3480 exceeds maximum 2712 for index "ix_data"
Here's my ...
4
votes
2answers
2k views
upgrading from postgres 9.1 to 9.3 on ubuntu server
I have my production server(ubuntu 13.10) running with postgresql 9.1.
I want to use few features of 9.3, hence want to upgrade.
Could someone help me with upgrading from 9.1 to 9.3 so that there is ...
4
votes
2answers
2k views
Configuring PostgreSQL for read performance
Our system write a lots of data (kind of Big Data system). The write performance is good enough for our needs but the read performance is really too slow.
The primary key (constraint) structure is ...
4
votes
1answer
2k views
Using Solr/Lucene for searching non-text tables?
I am creating a web application to retrieve subsets of one large (4m rows) table. The 4m rows only change once a year. The table has 200+ columns of types boolean and numeric. It has no text columns.
...
4
votes
1answer
5k views
How to use aes-encryption in PostgreSQL?
I tried aes-encryption by using following statement:
SELECT encrypt('test', 'key', 'aes');
which worked, but I am not able to decrypt the value. I inserted it in a field of datatype bytea but I'm ...
4
votes
2answers
2k views
Replication has failed; how to get going on again?
I'm running Postgres 9.1.6 on Ubuntu and I have streaming replication setup between a master and slave. Everything has been running smoothly until the database crashed and we had to restart both of ...
3
votes
2answers
307 views
Change built-in default privileges in PostgreSQL?
Introduction.
When I create a database,
postgres=# CREATE DATABASE test2 OWNER test2;
it is created with an empty privileges column:
Name | Owner | Encoding | Collate | Ctype | ...
3
votes
1answer
64 views
Very large btree index with few rows (openstreetmap gis data)
I tried to import a subset of open street map data to a Postgres database using the standard tool (osm2pgsql). I then tried to rebuild the indices on the table using plain sql (dropping them, then ...
3
votes
1answer
71 views
Use of integer instead of interval (of one type)
Our DB design presently has a interval column which will only be storing days (no other interval type) so it is making sense to use INT2 (smallint) instead of interval. Reference to documentation.
...
3
votes
1answer
489 views
Profiling PostgreSQL
We have a system built with Python 2.7, Django, PostgreSQL 9.1 and PostGIS 1.5. In this system there is some extensive validation processes that are, well, intensive.
Our machine:
SO: CentOS 6
HW: 8 ...
3
votes
1answer
1k views
delete rows in 3 tables with on delete cascade
For a relation 0..n between two tables (t1,t2), the mpd generates an intermediate table (t3) with two fk.
Let two constraints on the t3 table, each one with "on delete cascade", if I do:
delete ...
3
votes
1answer
2k views
PostgreSQL: Cannot change directory to /root
I am trying to copy a table planet_osm_polygon from one database osm to another test. I su postgres and performed the pg_dump.
Problem: However I'm getting the error could not change directory to ...
3
votes
2answers
537 views
PostgreSQL: Can the archive command for master and standby point to the same directory?
I use streaming replication and PITR (WAL files) in my cluster, currently I have different versions of postgresql.conf for the master and the standby server.
The only difference in the files is the ...
3
votes
1answer
234 views
Postgres Backup queries on AWS after reading Postgres docs
I am reading backup section of postgresql docs before heading over to create backup strategy on my EC2 instance.
So there are 2 ways to do it.
Create a dump using pg_dump.
File level backup with ...
3
votes
1answer
7k views
PostgreSQL: changing password for a user is not working
I installed PostgreSQL on EC2 machine and now I want to change the password of user postgres
I do
$ sudo -u postgres psql
psql (9.1.5)
Type "help" for help.
postgres=# ALTER USER postgres WITH ...
3
votes
2answers
2k views
PostgreSQL: How to backup only One Schema from a database and restore it on another server
I have a database named "A" which has two schemas "B" and "C".
I want to backup and restore Schema "B" on a different server? not sure how to do this as I am new to Postgres.
Do I have to create a new ...
3
votes
1answer
344 views
Postgres backup and WAL to S3
We are looking for a solution for the following problem:
We have set up streaming replication so we have a master DB and a slave DB, we want to have basebackups and WAL files sent to our S3 storage ...
3
votes
1answer
56 views
restarting postgresql quickly
I'm reading over the postgresql admin cookbook. Some of the database server parameters require a restart and with a busy database, you may need to restart quickly. There are a number of things to do ...
3
votes
1answer
339 views
Users groups/permissions when working with PostgreSQL
While I've been working with PostgreSQL for approximately 1 year, I still feel like a bit of a Newbie. I feel like I'm constantly fighting with psql and pg_dump when it comes to files and ...
3
votes
0answers
118 views
What happen when Postgresql tablespace is null?
I'm facing a DWH on postgresql with no DBA (I'm not one) so I turn to you with hope to figure this issue/s.
Long story short, when I check the disk usage per tablespace, I get different distribution ...
2
votes
3answers
1k views
Postgres connection access denied on IPv6 address
Installed PostgreSQL 9.1 x64 on Windows, set up a listen address, but when connecting with pgAdmin I get the following error. Not sure why PostgreSQL is seeing my IPv6 address and not my regular IP ...
2
votes
2answers
646 views
PostgreSQL performance degradation over time on a write intensive db
I have observed a weird situation that over time the performance of a query (a combination of queries explained below) degrades, meaning at the start of testing (for a few minutes) the time of the ...
2
votes
2answers
1k views
PostgreSQL difference between VACUUM FULL and CLUSTER
I have a table with 200 GB of size occupied by data and 180 GB of size by the 6 indexes on it. It is 30% bloated, so I want to reclaim unwanted space occupied by it. It is clustered on job_id_idx ...
2
votes
2answers
460 views
Is it a good idea to move high-volume and highly-accessed tables to a separate database?
Ours is a web-based application (built on multi-tenant architecture) running PostgreSQL v9.1.3. There are about 450 tables in our application, out of which 2-3 tables, specific to a module in the ...
2
votes
2answers
1k views
How to dump PostgreSQL database whose owner has no password?
First, some background on my setup:
The server has root access disabled. So, I log-in as (say) john who also belongs to the sudo group and is therefore able to run superuser commands.
I created a ...
2
votes
2answers
8k views
Forgotten PostgreSQL Windows password
This morning I’ve been trying to connect the Postgresql database on my Windows 7 professional desktop.
The default value is ‘postgres’, but sure enough I forgot what password I used when I originally ...
2
votes
2answers
287 views
How can I 'fake' stats for generate_series data?
With 9.1 and earlier, using generate_series in the select clause does not increase the expected row count (but of course does increase the actual row count), leading to possible poor planning:
...
2
votes
2answers
958 views
Select a range of integers
I have a 'range' of integers (in this case destination ports) that occur:
For example: 33848-33254.
I want to select 'all' of these without having to do one at a time. is there a range operator? Or ...
2
votes
1answer
466 views
Return ResultSet with column names from postgresql Stored procedures
I have the following postgresql Function:
CREATE OR REPLACE FUNCTION readMessage(messageFor INT, qid INT = NULL, ctxt INT = NULL, messageFrom INT = NULL, byTimeStamp BOOLEAN = FALSE)
RETURNS SETOF ...
2
votes
1answer
216 views
PostgreSQL 9.1, how to restore a Hot Standby Slave w/o any Wal Files?
I have a PostgreSQL 9.1 hot standby slave that had all of its wal files removed. How can I restart the standby server and have the slave resync from the master? Thanks
2
votes
1answer
63 views
Why is vacuum at a critical level in PostgreSQL when I'm running autovacuum?
I just checked the state of vacuum in my PostgreSQL database and it's at a critical level:
$ check_postgres --action=last_vacuum
POSTGRES_LAST_VACUUM CRITICAL: DB "postgres" DB: postgres TABLE: ...
