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

learn more… | top users | synonyms (1)

1
vote
1answer
22 views

is shared storage safe in Postgres if all servers are read-only?

Description of shared storage in Postgres: https://wiki.postgresql.org/wiki/Shared_Storage Related question: http://serverfault.com/a/620315/137464 Both of these make it clear that it's not safe to ...
6
votes
3answers
7k views

remove password requirement for user postgres

I understand that, upon installation, PostgreSQL has no password for its db root user (postgres): postgres=# select usename, passwd is null from pg_shadow; usename | ?column? ----------+---------- ...
1
vote
1answer
63 views

How do I insert a record and put the ID into an existing record in another table?

I want to insert a record in one table and save the resuting ID in a record I already have in another table. Which sounds easy. Simplistic example: create table aaa (id serial primary key, foo int);...
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 ...
3
votes
2answers
552 views

Slow fulltext search due to wildly inaccurate row estimates

Fulltext queries against this database (storing RT tickets) seem to be taking a very long time to execute. The attachments table (containing the fulltext data) is about 15GB. The database schema is ...
0
votes
1answer
9 views

postgres index for “?|” JSONB operator

I have a jsonb row with objects/dictionairies : {"a":1, "b":2, "c",3} I want to query rows that contain a set of keys, ex: SELECT * from my_table v WHERE v.my_jsonb_column::jsonb ?| array['b','a'] ...
0
votes
1answer
839 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....
0
votes
2answers
45 views

Are two logically equal indices physically the same index?

I have a table with this definition: CREATE TABLE public.delete_operation ( id bigint NOT NULL, parent_id bigint, pdu character varying, progress smallint NOT NULL DEFAULT 0, description ...
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 ...
1
vote
1answer
27 views

Remove accidental duplicates in PK table, cascade changes to FK table

Lets say I have three tables in a postgreSQL database, one of plants, one of samples, and one of mappings to change variables in plants. There is a PK-FK relationship between plants.id and samples....
3
votes
1answer
77 views

Optimise a LATERAL JOIN query on a big table

I'm using Postgres 9.5. I have a table that records page hits from several web sites. This table contains about 32 million rows spanning from Jan 1, 2016 to June 30, 2016. CREATE TABLE event_pg ( ...
0
votes
1answer
189 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 ...
4
votes
1answer
744 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 ...
2
votes
1answer
135 views

How to constraint a foreign key to a subset

I have a table employee, and each of them can fulfill different roles, like inspector and constructor. I also have another table with a column where I can store what inspector is on the job. People ...
1
vote
1answer
22 views

Postgres latency issues during memory compaction on EC2 (Ubuntu 14.04)

We've recently upgraded our EC2 instance that hosts our Postgres database to an i2.8xlarge with 244GB of memory (this is to utilise the large amounts of ephemeral storage it comes with). Since ...
1
vote
1answer
127 views

Upgraded Postgres; how to access now-missing databases?

My local connection on my Mac (10.9.5) wasn't working to my Postgres (9.3) database (and therefore I couldn't actually export the data), so I tried upgrading it to 9.4. Luckily I can now connect to ...
1
vote
2answers
46 views

How to find out if rows are refenced from other tables?

I have a table named contacts (primary key id) which is referenced by a number of other tables via foreign keys. When a list of contacts is displayed in the application, I get their primary keys in an ...
0
votes
1answer
611 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 ...
4
votes
3answers
305 views

Which tool to compare an Oracle database with a Postgresql database?

We are currently migrating an Oracle database 10gR2 to a Postgresql database 9.3. Thanks to Hibernate, all tables can be recreated seamlessly. However, the Oracle database is huge. We are going to ...
22
votes
2answers
17k views

Optimizing bulk update performance in PostgreSQL

Using PG 9.1 on Ubuntu 12.04. It currently takes up to 24h for us to run a large set of UPDATE statements on a database, which are of the form: UPDATE table SET field1 = constant1, field2 = ...
0
votes
1answer
26 views

PostgreSQL - cannot create RULE from stored procedure

I'm trying to automate boring database work. I would like to have one function which creates views, rules, etc so I don't need to run corresponding SQL queries from file. Currently I faced an issue ...
0
votes
0answers
16 views

Backup and Restore Hive Metastore in Postgres

How to backup and restore hive metatstore in embeded postgresql for CDH5.7.0 cluster? If the table is drop, then how to restore the metadata for that particular table in embeded postgresql.
0
votes
1answer
23 views

how to check list of users present in all postgres database

I have around 50 of postgres database. I need to check all users with their respective database name. I know \du gives the list of all users like, List of roles ...
0
votes
0answers
21 views

Traditional SELECT with Multiple Joins or SELECT JSONB then multiple SELECT

I am writing a simple recipe book app based on node.js with sequilize as ORM and Postgresql 9.4 as DB. Recipe has multiple ingredients that can be measured with different units. I store recipe steps ...
0
votes
0answers
17 views

Is Schema Level Replication possible in PostgreSQL?

I am very new to the world of databases and wanted to know how one would/could accomplish Schema level replication in PostgreSQL. I've read about Slony, but it does not appear to state anything about ...
-1
votes
0answers
15 views

PostgreSQL Table Function: return all table fields

I have already created a view which has my derived columns with appropriate aliases. I would like to create a table function which uses this view and returns all the columns from that view. I have ...
7
votes
1answer
262 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 ...
0
votes
1answer
171 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 ...
3
votes
2answers
1k views

Optimize simple query using ORDER BY date and text

I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records The data types of the table are: fcv_id = serial fcv_fecha_comprobante = ...
3
votes
1answer
18 views

What can I do to monitor “out of shared memory” issues?

I'm new to database administration and I would really like some advice on what tools I could use or what I could do to monitor "out of shared memory" issues. I've been seeing these "out of shared ...
0
votes
0answers
23 views

pg_controldata 'Time of latest checkpoint' on standby doesn't change

About 2 weeks ago, pg 9.3 master space filled up, didn't die, continued operation after space was freed up, and it's standby has kept up with it ever since. However the standby pg_xlog keeps filling ...
24
votes
2answers
30k views

Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

I have a PostgreSQL 9.2 instance running on RHEL 6.3, 8-core machine with 16GB of RAM. The server is dedicated to this database. Given that the default postgresql.conf is quite conservative regarding ...
9
votes
1answer
411 views

Storage snapshots for consistent backup of postgresql - different data and log volumes

We are running many Linux VM's in a vmware/shared storage environment, each running its own instance of postgreSQL (a mix of 9.0 and 9.3). Currently, the entire VM sits on a single root partition/...
0
votes
1answer
15 views

Postgres: SELECTing bytea data partially with offset and length

I have a table containing a bytea column, with an average entry size of 5MB. Is it possible to query from random points in the bytea data (given an offset and length of data required) instead of ...
0
votes
2answers
19 views

PITR restore in postgresql

I need to perform incremental backups and restore those backups in postgresql. So in order to achieve this i setup wal-archiving as follows. wal_level = archive archive_mode = on archive_command = ...
0
votes
1answer
275 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
26 views

postgresql reindex frequency and monitoring

What's up guys. I have a couple of tables where i inserting much of data (and almost never deleting). Periodically i see that insert gains ExclusiveLock on index of table. So i guess that sometimes ...
0
votes
1answer
21 views

plpgsql function to replace ' ' with null after insert using a trigger

I have a table in PostgreSQL 9.5 on which insert statements are run frequently. After an insert statement was ran on the table there are ' ' (single quote, space, single quote) values when no data was ...
6
votes
2answers
2k views

Ways to speed up IN queries under PostgreSQL

I have a query of the following form: SELECT * FROM twitter_personas WHERE twitter_user_id IN ($1, $2, $3, ..., $25000) The IN query has anywhere from 10 to 25000 values. The query runs for minutes ...
1
vote
2answers
51 views

Full Join Query

I am doing a full join query from two tables. SELECT i.idinasistencia, i.fecha, i.valor, i.justificado, h.id, p.nombres, p.apellido_paterno, p.apellido_materno, p.rut_persona FROM historialalumno ...
0
votes
0answers
10 views

how to enable shp2psql-gui in pgadmin3 with postgresql-9.5 on centos

I have installed postgresql-9.5 on centos with postgis2_95 extension. Now I want the shp2psql-gui plugin tobe enable in my pgadmin3. Please help on this as I am unable to enable it though I have ...
3
votes
1answer
58 views

How to LEFT JOIN a date table onto each customer (GROUP BY) in a customer purchase table? Can a JOIN be done for dates after initial purchase?

I am stuck on implementing a left join on dates for each individual customer. I have a table which stores customer purchases: SELECT purchase_date, customer FROM purchases I would like to create a ...
13
votes
1answer
2k views

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

I have a legacy schema (disclaimer!) that uses a hash-based generated id for the primary key for all tables (there are many). An example of such an id is: 922475bb-ad93-43ee-9487-d2671b886479 There ...
1
vote
1answer
131 views

Postgresql: Optimize finding all distinct JSON keys from a colulmn

I'm looking for a way to index / optimize finding only the keys in a JSONB column. The use case is an ETL operation where I need to take data stored in the JSONB column and output it as delimited ...
1
vote
1answer
31 views

Median absolute deviation for time series outlier detection in Amazon Redshift

Context I am tasked with trying to detect outliers in time series data in an Amazon Redshift (PostgreSQL) system. Also known as a public holiday detector around the office. The method I have been ...
2
votes
2answers
150 views

Use array of composite type as function parameter and access it

I have created a type Books in Postgres which has 2 numeric fields and 2 varchar fields. I want to send an array of Books to a function to INSERT those values in a matching table. This is my type: ...
0
votes
0answers
17 views

Restore Postgres Database with Old Data Directory

I have not made a backup from my old data directory, but I still have the directory in tact. In my current database I have the same Schema, etc... When I try to restore the database (using pgadmin III)...
0
votes
1answer
73 views

CTE Query copy value if exists into an existing table [closed]

I got the following query: WITH table_data AS ( SELECT UNNEST(array['Small Bus', 'Window', 'Test 1']) AS description, UNNEST(array['1dfsdf4', 'QSF-312', 'EXD-TUH']) ...
0
votes
1answer
21 views

Can I pass a TABLELIKE parameter to a PostgreSQL FUNCTION/PROCEDURE?

I have a function which returns: TABLE (id TEXT, collected TIMESTAMPTZ) In another function, I would like to be able to pass the result of that first function as a parameter. For example: CREATE ...
1
vote
1answer
39 views

Impact of OS timezone change on Postgres Database

I want change OS time zone to UTC. This will shift back time by 2h. Can this possibly damage data in any way (for example overwrite data)? What should I do to safely change the OS time zone?