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
1k views

pg_basebackup giving “could not receive data from client: Connection reset by peer”

I'm trying to get data to a new slave box from my new master (previously the slave). I've tried the steps already and it worked previously. Now when I run the following command: pg_basebackup -h ...
3
votes
1answer
41 views

help in the initial configuration of postgreSQL9.6

I recently installed postgreSQL9.6 version on windows 7 64 bit machine. During the installation process, I was asked to key in the super user password and the locale of the database (I chose default ...
1
vote
1answer
27 views

Postgresql perl : “NOTICE: elseif should be elsif at line …”

It's not the first time I get this strange NOTICE when adding perl functions in Postgresql : NOTICE: elseif should be elsif at line 11. This happens at CREATE FUNCTION as well as when using the ...
6
votes
2answers
279 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
1answer
48 views

2ndQuadrant BDR installation not working anymore

I previously used the following to install BDR (9.4) on Ubuntu: echo "deb http://packages.2ndquadrant.com/bdr/apt/ trusty-2ndquadrant main" > "/etc/apt/sources.list.d/2ndquadrant.list" wget --...
26
votes
12answers
73k views

PostgreSQL is running locally but I cannot connect. Why?

Recently updated my machine from Mac OS X Lion (10.7.4) to Mountain Lion (10.8) and I think it borked my PostgreSQL installation. It was installed originally via Homebrew. I'm not a DBA, but hoping ...
1
vote
1answer
33 views

Is it possible to set PostgreSQL roles with privileges this way?

I'm trying to develop a system, and for the first time I'm using PostgreSQL. I'm trying to organize and separate roles properly, but I'm losing my mind with it (about a week on this now). Following ...
0
votes
1answer
160 views

Postgresql Streaming replication - monitoring

I have setup streaming replication for my postgres servers. I am using PostgreSQL 9.2.6. Replication seems to be working fine. I am monitoring this with two ways using nagios: Log_delay and ...
0
votes
1answer
606 views

pgpool2 not working on my Ubuntu i686 athlon i386 GNU/Linux

I am trying to implement replication and load balancing on my Ubuntu i686 athlon i386 GNU/Linux.for creating database clustures I have installed postgresql contrib 9.1.11 having initially database ...
7
votes
2answers
142 views

What are the consequences of not specifying NOT NULL in PostgreSQL for fields which can't be null?

I have an application (data is stored in PostgreSQL), where majority of the fields in the tables are always not null, but the schema for these tables does not enforce this. For example look at this ...
0
votes
1answer
249 views

can't install citext extension: could not access file “$libdir/citext”: No such file or directory

I'm trying to get citext available as a type in a specific database. The Postgres database server is 9.4. I see it on the server in the pg_catalog's pg_available_extensions view. From the psql ...
0
votes
1answer
721 views

Tomcat7 JDBC connection pool -> Connection has been abandoned

I have the following configuration for the JDBC connection pool initialSize="2" minIdle="10" maxIdle="50" maxActive="100" maxWait="15000" jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor....
1
vote
1answer
34 views

Select rows and add their previous rows

I have a table structure that holds historical data for another table. Something like id | name | date ---------------------------------------- 1 | John | 2016-01-01 14:38....
1
vote
1answer
18 views

Can I grant privileges to a Postgres user on all schemas belonging to a database with one command or script?

Let's say I have 20 different schemas on a Postgres database. I create a new user theUser and now I want to grant only the SELECT and INSERT privileges for this user on all schemas. Is there an easy ...
0
votes
2answers
74 views

Increasing Postgresql 9.5 max_connections to at least 300

Right now I am using the default postgresql.conf which has maximum connections set to 100. I need to increase max_connections to 300. Postgresql 9.5 is the only thing running on my Linode. I am ...
9
votes
3answers
20k views

PostgreSQL 9.1 Hot Backup Error: the database system is starting up

I have been working on a hot backup for Postgres 9.1 for awhile and have run into a consistent issue. After restarting Postgres on the slave server the pgstartup log file and the daily log file under ...
1
vote
1answer
35 views

Consolidate multiple rows into single row

In PostgreSQL 9.5, I have a table named reports: CREATE TABLE public.reports ( id BIGSERIAL PRIMARY KEY, id_station character(11) NOT NULL, date date NOT NULL, element character(4) NOT NULL, ...
1
vote
0answers
23 views

How do I organize database initialization so it can be executed in a fixed order?

I am trying to update an established database system to leverage change management. The main point is to make the development and production environments follow the same process. I need help ...
1
vote
2answers
63 views

Read only postgres table, except by trigger

Suppose the following situation: I have a product_stock table and a stock_transaction_log table. Currently whenever there is an increase/decrease in a product's stock, I update the product_stock ...
0
votes
1answer
40 views

Unable to backup postgresql database. pg_dump memory alloc error

After 3 days of running an import routine written on RoR to move some 269,000 records from a legacy database via csv files the routine finally finished. The very first thing I attempt to do is to ...
0
votes
0answers
43 views

Postgres query taking one minute to fetch one row

I am using Postgres version 9.4 in my application. I have a partitioned table named sql_sent_sms, which is partitioned on the basis of primary keys. I have a query which uses join on same table with ...
0
votes
1answer
22 views

Create pgAdmin database on local machine

I am trying to create databases on my local machine using pgAdmin. When I try to connect to a new server using the "New server registration" entry below it gives me the "Server doesn't listen" error (...
0
votes
1answer
179 views

pg_get_viewdef error while running pg_dump in PostgreSQL

Occasionally, I get the following error while doing a simple pg_dump ( pg_dump -U postgres -Fc -f mybackup.dmp mydb): pg_dump: [archiver (db)] query failed: ERROR: could not open relation with OID ...
0
votes
0answers
54 views

Pgpool not load balancing

I am configuring Pgpool to load balance select queries among a master, and two slaves. However, all queries are still going to the master server, and none to the slaves. I have set load_balance_mode, ...
1
vote
2answers
31 views

Repeat total count value

I have a table called paths that looks like this: id parent_id -- --------- 1 null 2 1 3 2 4 3 5 4 6 5 7 5 8 5 9 ...
1
vote
0answers
31 views

Querying pg_buffercache: relation “pg_buffercache” does not exist

I have PostgreSQL 9.4.7 installed in LXC container on Ubuntu 14.04. For monitoring purposes, i try to execute this from my host machine: psql -qAtX -h *** -p *** -U postgres -c "select count(*) from ...
4
votes
1answer
723 views

Get inserted row count when master table isn't modified

I have a partitioned table where data resides only in the child tables. The master table has an insert trigger that will distribute inserted data into appropriate child tables. Now, I need to create ...
0
votes
1answer
31 views

pgAdmin crashes or freezes when big MATERIALIZED VIEWs are refreshing

I'm using pgAdmin III v1.22.1 on Windows 7 to connect to a PostgreSQL server version 9.4.8 on Red Hat. We have scripts to REFRESH MATERIALIZED VIEWS during the night. When they are refreshing, I can'...
7
votes
1answer
245 views

Surprising results for data types with type modifier

While discussing a recursive CTE solution for this question: Get the last 5 distinct values for each ID @ypercube stumbled across a surprising exception, which lead us to investigate the handling ...
3
votes
3answers
50 views

SQL, category tree with multiple parents

I need to make a category tree where some leafs can have multiple parents, for exaple g \--b \--a c \--d \--a above category 'a' has two parents(g and c) My first attempt was to create the ...
2
votes
1answer
37 views

Optimal query to show active records by date

I'm trying to determine what the optimal query might be for associating a date with a record that would have been active for that day. Here is a setup to demonstrate what I'm trying to do (demo at ...
0
votes
1answer
38 views

Database in postgresql with missing rows, how to fill the missing rows with a backup copy?

I have a big problem and I'm not that good with postgresql since I use Django to make queries and I only have the basics of sql for querys. My problem is that there was a problem where a super user ...
1
vote
1answer
1k views

pgpool-II configuring multiple master databases

I could not find a solution for this anywhere, not even in the pgpool manuals. How do I configure multiple - master postgresql databases with pgpool-II? Additional requirement is that each master ...
0
votes
1answer
42 views

PostgreSQL9.6 Beta1:new setting remote_apply for configuration parameter synchronous_commit

PostgreSQL9.6 add new setting remote_apply for configuration parameter synchronous_commit, Reading the following document I still don't understand the difference between on the setting on and ...
0
votes
1answer
25 views

Why my Postgres 9.3 database is logging crazily autovacuum alerts

The latest days, Postgres has started to log very frequently (about 100 logs per second!!!!) this kind of traces: autovacuum: found orphan temp table "pg_temp_5"."p14528" in database "...
0
votes
1answer
22 views

Viewing disk usage of temporary buffers of running queries in Postgres

I know how to get the sizes of relations or total table disk usage per table in Postgres 9.5 (https://wiki.postgresql.org/wiki/Disk_Usage), but I have some queries running whose temporary disk buffers ...
1
vote
1answer
35 views

Count approximation with where clause

I have the following problem, we have a table of facilities for apartments that looks something like this: I would like to perform the following query SELECT crawled_name, count(f.id) AS count ...
0
votes
0answers
33 views

postgresql Idle for transaction while migrating 30GB database

I'm migrating part of a mysql database into a postgresql database with a different Schema. I've written a python script, using mysql-connector and psycopg2 modules. It takes care(should) to convert/...
3
votes
0answers
98 views

Inconsistent equality checking of Unicode text in Postgres 9.5?

Using PostgreSQL 9.5. It seems like Postgres is being inconsistent with how it compares strings similar to لى. A unique constraint is considering some strings equal that GROUP BY is considering ...
9
votes
2answers
252 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
1answer
65 views

Placing a uniqueness constraint on a date range

I have a table reservation with columns roomno(INTEGER), startdate(DATE), enddate(DATE) with a primary key on (roomno, startdate). How do I place a constraint on the table such that bookings are not ...
0
votes
1answer
26 views

Postgresql: Storing a table index on a separate disk to the data

I have development a postgresql 9.5 database which I originally set up on an SSD. However, as this project will grow to ~200TB (and likely more), I have moved my current test of ~200GB on to a HDD, ...
2
votes
2answers
197 views

Limiting read access in postgres to few rows

We have a requirement where we want to limit the maximum no. of rows that a select query can return when its done using a particular login. So basically we would like to append a limit clause to any ...
1
vote
1answer
23 views

Insert column name as value

Consider the following, in PostgreSQL: drop table test ; create table test (result1 text, red smallint, green smallint, blue smallint, results2 text) ; insert into test values ('red',1,2,3) ; I ...
3
votes
1answer
42 views

Why does PostgreSQL scan a lot of rows?

I have a small table of 135,000 rows. Here is the schema: id integer NOT NULL nextval('history_sum_id_seq'::regclass) zone_id integer NOT NULL spot_id character varying(24) NULL::...
0
votes
2answers
1k views

pgAgent : how to configure pgpass.conf for a local connection on Windows Server 2008

I try to configure pgAgent on Windows server 2008 to make automatic backup on the database. pgAgent is installed on the server and the service is running normally. I use PgAdmin3 and PostgreSql 9.3 I ...
6
votes
1answer
17k views

Update all columns from another table

I need to update a table from another one, and I need to update all columns. Besides listing every column in the SET clause, is there a way to update them all at once? Like this: update tableA set * =...
1
vote
2answers
2k views

What is best strategy for backup on streaming replication(hot standby) environment

I have questions. What is best strategy for backup on streaming replication(hot standby) environment. I’d like to do backup on slave server and restore(PITR). First, I set up streaming replication(...
4
votes
1answer
401 views

Uniqueness constraint with date range

Consider a prices table with these columns: id integer primary key product_id integer -- foreign key start_date date not null end_date date not null quantity integer price numeric ...
3
votes
1answer
148 views

PostgreSQL lost connection during massive index operation? What exactly happened?

Using PostgresQL 9.4, Ubuntu 14.04, and 32 GB RAM (with modified settings in postgresql.conf file; I think the highest allocation is 16GB). I ran a ts_vector creation on a text column in a table ...