All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

2
votes
2answers
48 views

How to unite 2 subqueries to reduce retrieving data from cumbersome tables?

There are 3 tables: user, conversation, message. Table user: id, login, last_login_time (time of last user's login), ... Table conversation: id, user1_id, user2_id, ...
0
votes
0answers
47 views

How can a select max query return no rows?

I have a process that selects the maximum date from a Greenplum (PostgreSQL 8.2) table, and coalesces it to a default value. Once, and once only, this query returned 0 rows. How is this possible? Even ...
0
votes
1answer
25 views

psql variable holding text with multiple lines

This works as expected, \set x '''2\n3''' but this doesn't \set x '''2 3''' Is there a workaround for later example so I don't have to use \n instead of newline?
0
votes
0answers
7 views

Debian Server failover setup

I need to set up two servers with failover capability (Active - Passive model) sharing a common DB (Postgres)running over NAS. Could you please suggest me some workaround for the same ?
0
votes
1answer
28 views

Migrate Oracle BLOB to Postgres BYTEA or OID [duplicate]

After the BLOB columns of Oracle (11g r2) tables are migrated into a Postgres (9.4.5) table such as using Ora2Pg, I have two questions: 1) Which data type will the new column assume, is it BYTEA or ...
2
votes
1answer
45 views

PostGIS - indexing - speed up query

I have this query (called from PHP, hence the $ variables): SELECT a.name, St_AsText(a.geom), b.name AS name_b, b.id FROM a LEFT JOIN b ON b.id = a.b_id WHERE ...
1
vote
1answer
31 views

PostgreSQL: Restoring 1 file from a directory format dump

I've got a large'ish (> 700GB) PostgreSQL database. I'm working on importing it into AWS RDS. I realize there are modules that will cause problems, we're dealing with that separately, so for right ...
1
vote
1answer
40 views

How do you configure Postgres 9.4 for incremental backups and hot standby?

I want to set up Postgres to do incremental backups. I'm using PostgreSQL 9.4 on Windows with a database that weighs about 1GB and has a growing 10000 records each day. I understood that the steps ...
0
votes
0answers
12 views

empty tablespace in postgres [duplicate]

With SELECT spcname FROM pg_tablespace; one can list tablespaces in Postgres. However, other than using du, how can one determine whether a tablespace is empty or not? If one tries to drop a ...
1
vote
1answer
25 views

How to automatically drop and restore PostgreSQL database

I am using dhis2 with PostgreSQL as database but I want to verify my backups from the cloud by restoring and checking whether the data are ok but I need it to be automatic.
3
votes
0answers
58 views

Recovery from Live to a new Slave Server - PostgreSQL - ERROR

I've started a new SLAVE PostgreSQL server set up. master: 192.168.100.1 slave1: 192.168.100.2 slave2(NEW SLAVE) 192.168.100.3 * NOTE: I run the pg_basebackup from another STANDBY SERVER. Not ...
1
vote
1answer
35 views

Postgres Can't Add Foreign Key Constraint

I have a table with about 220 million records :( and I need to add a foreign key constraint. My command looks something like this: ALTER TABLE events ADD CONSTRAINT events_visitor_id_fkey FOREIGN ...
1
vote
0answers
131 views

Is it safe to update postgres server to 9.5 with ubuntu automatic update

Postgres 9.5 is out and Ubuntu has it in the update manager already. I'm wondering though, is it safe to update that way? Concerns/Questions: Does it install 9.5 in addition to my already running ...
7
votes
1answer
109 views

Postgres with pgpool architecture

Below is an example pgpool architecture: This implies that you only need to have pgpool on a single server; is this true? When I look at the configuration I also see that you configure backends ...
0
votes
0answers
22 views

Postgres Public Synonym Issue

PostgreSQL ver 9.4.5. Linux OS. Application: Web Based Platform: App Server (java) --> jdbc call --> Database Server (PostgreSQL) I do know that PostgreSQL does not support the public ...
0
votes
1answer
31 views

Do Foreign Keys Impose Uniqueness

Do FOREIGN KEYs impose the uniqueness of the the referent? I.e. if I have a foreign key that refers to a UNIQUE constrained column, will the FOREIGN KEY column be unique? I'm particularly wondering ...
1
vote
2answers
25 views

Recreate all foreign keys in all tables as deferrable (batch)

I want to make all foreign keys in my database deferrable. But there is no possibility to alter an existing constraint. So I need to drop and add again each foreign key. How to do it automatically?
2
votes
1answer
26 views

Replace content of only one table with new version (should disable foreign keys?)

I have 2 tables. 2nd has foreign keys to reference 1st. I need to replace the whole content of the 1st table with the new version from another database. The new version satisfies all constraints and ...
0
votes
1answer
29 views

New column to a base table

I want to add a new column to a base table, and add that new column to an existing view... So all selects/updates/deletes translate back to the original table How can I do that? Thanks ...
7
votes
1answer
86 views

Why does adding a smallint column to a postgres query increase execution time 60X?

Here I have what seems to me to be a routine table and a table creation script that I have put in /tmp/try.sql. I am running postgres under OSX 10.10.2. Here is the table: cow_dev=# \d carc ...
2
votes
2answers
45 views

PostgreSQL is using more memory than I'd expect

I have PostgreSQL configured mostly how pgtune recommended, and the settings are supposedly conservative. PostgreSQL is using more memory for a single connection than the settings would suggest is ...
0
votes
0answers
26 views

Is it possible to do a VACUUM FULL and allow read-only access to the table?

I have a PostgreSQL (9.3ish) database which has a lot of data written to it frequently. Disk space is constantly being used. I have had success with VACUUM FULL with trimming the bloat and reducing ...
9
votes
1answer
386 views

What is the overhead for varchar(n)?

I wanted to ask for the meaning of this fragment from Postgres doc regarding varchar(n) type: The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which ...
0
votes
1answer
41 views

Three queries returning 1 row each

So.. I've got: 3 DB Queries returning 1 row each .... SELECT value FROM ja_settings WHERE varname='clickatel_api_id' SELECT value FROM ja_settings WHERE varname='clickatel_user' SELECT value FROM ...
6
votes
1answer
60 views

Index not used with `=any()` but used with `in`

Table t has two indexes: create table t (a int, b int); create type int_pair as (a int, b int); create index t_row_idx on t (((a,b)::int_pair)); create index t_a_b_idx on t (a,b); insert into t ...
2
votes
1answer
30 views

Postgres plpgsql: Is a view better than a select query in a plpgsql function

I have a recursive query to the general effect of: WITH RECURSIVE text1 (selfid, parentid, text) AS ( SELECT DISTINCT text.selfid, text.parentid, text.text FROM text WHERE text.selfid = ...
1
vote
0answers
42 views

Why does this function return an error by itself, but when called by another function it doesn't return an error?

I have a function that calls a sub-function as part of the function. When running the sub-function with the same data it throws an error, when running the main function it doesn't. I would have ...
1
vote
1answer
50 views

Simplest filesystem backup of Postgres database

Situation: A backup system performs filesystem-level daily backups on a server containing a large but fairly static PostgreSQL 9.2 database. The filesystem backups are incremental and generally take ...
0
votes
0answers
35 views

How to get CPU and memory usage of PostgreSQL server and queries from database system views?

We are looking for the way to get CPU and memory usage data of PostgreSQL 9.x for the monitoring purposes. The requirement is to provide the data on server, database, session and query level. We are ...
3
votes
0answers
102 views

What's the point of prefixing each element in the column by its column number in multicolumn GiN indexes?

According to these slides which, I believe, are incorporated into the current implementation, each column element in multicolumn GiN indexes is prefixed by its column number and is stored separately ...
1
vote
1answer
140 views

Slow query - Optimize - PostgreSQL

I've got a slow query here... could you please help me to optimize it? Query: SELECT concat(company, ' ', customer_name_first, ' ', customer_name_last) AS customer, sum(revenue) AS revenue, ...
4
votes
1answer
46 views

Deadlock best practices

We are running Postgres 9.3, and we have occasional deadlock issues. Currently someone notices and will manually kill off the processes. Is there a best practice in dealing with these when they ...
3
votes
1answer
80 views

Versioning data in a database [duplicate]

I have a database to hold data about projects. Projects have lots of data associated with them that is segmented into other tables such as project_contacts and project_images. Those are one-to-many ...
1
vote
1answer
44 views

Does ALTER on PostgreSQL, Oracle table (respectively) create intermediate copy of the table?

I've heard MySQL admin claim that ALTER on MySQL table creates an intermediate copy of the table during the operation (don't know if the type of table involved were supposed to be MyISAM or InnoDB), ...
3
votes
1answer
15 views

How can I exclude range overlaps if one range may be empty?

Suppose I want to ensure that, for any expected height in feet, I have a single, unambiguous classification. I create this table: CREATE TABLE heights_in_feet( size TEXT NOT NULL, min INTEGER NOT ...
1
vote
1answer
94 views

Mapping between each bit in a bitmap index and tuple-pointers

Bitmap Indexes are described here, but I don't understand some part of it. A plain indexscan fetches one tuple-pointer at a time from the index, and immediately visits that tuple in the table. ...
2
votes
0answers
53 views

Postgresql Streaming Replication Error: WAL segment removed

I want to set up PostgreSQL streaming replication, but get the following error: FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 00000001000000000000006A has already ...
1
vote
0answers
53 views

How to fix postgres stuck in recovery mode?

I have Jira and Confluence installed in a server. We didn't have any free disk space one day, and these services didn't started correctly, so after deleting some files we now have some free space ...
6
votes
1answer
348 views

What happens when we add an index to an existing table with a large amount of data?

I've a table which will contain around ~15 Million records. Now I need to add an index to the table. Adding an index will take some time to update every entry in the table. I'm quite confused ...
5
votes
0answers
144 views

To partition or not to partition?

Having already read several questions on SO, external blog posts and manual SO: Foreign Key constraint to partitioned table in Pg dba.SE: Different ways of handling FK to partitioned table in Pg ...
2
votes
2answers
87 views

Filesystem tuning for PostgreSQL

I want to install PostgreSQL 9.x version in an Ubuntu 14.04 workstation and I'm looking for a recommendation on how to set it up in order to get a good read-write (50/50-balance) performance keep a ...
4
votes
1answer
67 views

Improving sort performance in GROUP BY clause

I have two tables in Postgres 9.4.1 events and event_refs with the following schemas: events table CREATE TABLE events ( id serial NOT NULL PRIMARY KEY, event_type text NOT NULL, event_path ...
3
votes
1answer
27 views

Postgres - estimate end time for a long update

I have currently running a long update (~million rows based on a postgis st_within condition) that was expected to finish in an hour or so based on similar previous queries. However, it's been three ...
2
votes
1answer
30 views

Basic transactional DDL script in PostgreSQL

PostgreSQL newbie here trying to put together a transactional DDL script to atomically create a database and its schema in Postgres 9.3: in a transaction, create a few tables; if any errors, roll the ...
0
votes
1answer
42 views

How to return a set of rows from this function?

I am new to Postgres functions and have a table with the following structure: CREATE TABLE options.options ( delta double precision, gamma double precision, rho double precision, theta double ...
0
votes
0answers
22 views

Postgres 9.2 replica can't catchup to master

I have configured a master/replica setup for postgres 9.2 on Amazon Linux. PS output shows that the replica is connected and streaming, but also recovering. This has been going on for about 24 hours. ...
0
votes
0answers
15 views

Where to put snowball files to add a snowball stemmer to PostgreSQL database?

I've downloaded Czech snowball stemmer source files (.c, .h files) and compiled them into .o files and I know the correct SQL syntax for adding the dictionary (it's in the docs). I keep getting the ...
5
votes
1answer
95 views

Index not being used in SELECT query

I have a table of about 3.25M rows with the follow format in Postgres 9.4.1 CREATE TABLE stats ( id serial NOT NULL, type character varying(255) NOT NULL, "references" jsonb NOT NULL, ...
1
vote
2answers
69 views

Postgresql text column character Limit

Im not even close to a DBA, so plain English please. I recently needed to look into a performance issue on a relatively small PG database, but yet is performing very slow. the server is running PGSql ...
0
votes
1answer
30 views

postgresql.conf file found in both /usr/share/postgresql and /etc/postgresql/

While troubleshooting another issue I noticed that in my Ubuntu 14.04 system there are two postgresql directories containing a postgresql.conf file: /usr/share/postgresql /etc/postgresql/9.3/main/ ...