Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

1,987 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
8
votes
0answers
499 views

Why does RDS PostgreSQL always recover on restart?

I am running an RDS instance of PostgreSQL 9.6.3: select version(); returns PostgreSQL 9.6.3 on x86_64-pc-linux-gnu [...]. I have found that after issuing a stop from the RDS console followed by a ...
6
votes
1answer
125 views

Which data model/schema to apply to a timeseries datastore for data sources with varying fields

I'm asked to develop a data-storage for time-series data, yet despite significant research I'm unsure about the data model and storage technology to chose. About the data The source data which is ...
6
votes
1answer
2k views

pgAdmin and PostgreSQL compatibility matrix

Has anyone ever put together a version compatibility matrix for pgAdmin versions and PostgreSQL versions? https://www.pgadmin.org/download/windows.php https://www.postgresql.org/download/windows/
6
votes
1answer
406 views

Postgresql with just index storage

I have a PostgreSQL table with a key (bigint) and a value (double). The table has tens of billions of rows. I have a single btree on the (key,value) for aid lookups by key. The table is never updated. ...
6
votes
0answers
383 views

Roles and policies in Postgresql 9.5 row-level security

Following the the info in posts like these i would like to use the role system and policies, i.e. each user in my system would have a db role. I want to model the following properties but i can't come ...
5
votes
1answer
352 views

How to limit work_mem on PostgreSQL

As an analyst I love having the ability to turn up the work_mem on occasion when a big sort or index build is in the offing. As a DBA, the idea of random users being able to request an arbitrarily ...
5
votes
1answer
281 views

PostgreSQL - How does multicolumn B-Tree index work with order by on 1st column and IN lookup for 2nd?

I created such table (similar to example from http://use-the-index-luke.com/sql/example-schema/postgresql/performance-testing-scalability ) CREATE TABLE scale_data ( section NUMERIC NOT NULL, ...
5
votes
1answer
534 views

Disallowing table creation in the public schema

Postgres version: 9.3.16 Let's say we have two users, luser and editor. I'd like to make it so that luser (or any other non-superusers) cannot create any tables under the public schema, except editor....
5
votes
1answer
1k views

How to exclude tables when using pg_dumpall with postgresql?

If use pg_dump, it has a --exclude-table-data option to ignore special table. But pg_dumpall command doesn't has that option. So how to exclude several tables when dump all databases?
5
votes
0answers
3k views

Postgres 9.6.1 Full Text Search dictionaries for most spoken languages

Using Mac OSX Yosemite 10.10.5, I am trying to run full text search operations, such as to_tsvector, to_tsquery, etc and have a need for dictionaries in about 80+ languages. Postgres seems to only ...
5
votes
0answers
581 views

How to minimize access exclusive table locks while reducing bloat or ALTER (ing) a table?

In some circumstances, I've been told not to do a VACUUM FULL (or CLUSTER) of a table that is in production, because that will exclusively lock it for longer than wished. The same might apply to ...
5
votes
0answers
1k views

Why does pg_last_xact_replay_timestamp() sometimes return extremely old timestamps?

In PostgreSQL 9.5, to monitor streaming replication lag, I use a combination of: pg_current_xlog_location() pg_last_xlog_replay_location() pg_last_xact_replay_timestamp() The first two functions are ...
5
votes
0answers
1k views

Slow Postgresql query with left join

I have a query on Postgres, and I also have added proper index. Is there anything missing here? SELECT orders.*, demo.name as d_name FROM orders LEFT JOIN users as demo ON demo.id = orders.dr_id ...
5
votes
0answers
435 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 ...
5
votes
0answers
2k views

postgres \copy file to RDS timeout

Postgres version 9.3, hosted on Amazon RDS. I have a messages table with 10G of data. I connected the database using psql on the EC2. Then used \COPY (select * from messages) TO '/tmp/messages.csv' ...
5
votes
0answers
814 views

How to modelling a union type in PostgreSQL

We have the generic idea of a rule with specific implementation details stored in their own table. We currently have the following schema: Rule * id * name * ruleAId * ruleBId * .... * ...
5
votes
1answer
1k views

Poor performance in my pgpool cluster

I've configured a postgres cluster with pgpool2 using the following link. The replication seems to be ok, but I ran some benchmarks (pg_bench) and the performance is lower than a single node, for ...
4
votes
0answers
48 views

PostgreSQL 10 backend processes

I have a particular issue with PostgreSQL 10: Postgres creates backend processes for each connection on different ports. The problem is that some of our apps also use many ports and sometimes they ...
4
votes
1answer
134 views

Calculating balance sheet with a single query

I have this schema for double entry bookkeeping system: CREATE TABLE account ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, parent_id INTEGER REFERENCES account(id), UNIQUE (name, ...
4
votes
0answers
107 views

PostgreSQL autovacuum ignoring “inactive” tables resulting in “transaction id wraparound”

I have a PostgreSQL database with a lot of tables (currently 93k) and I'm seeing issues with transaction id age threatening wraparound, because autovacuum is not getting triggered for clean tables ...
4
votes
1answer
608 views

Is there a strategy to use timestamp as primary key?

The majority of times we use integer sequences for primary keys to maintain uniqueness of row in a table. Couldn't we use timestamps instead of integers for this purpose? Is there any flaw in that ...
4
votes
1answer
185 views

Recommending movies customers haven't rented yet based on their favorite genres, according to their rental history

Disclaimer I do NOT want any explicit queries to my question. I want suggestions regarding how I can best approach this problem. I am eager to learn SQL by experimentation, but for my sake, I'd ...
4
votes
1answer
399 views

Test Driven Design for postgres procedures

I am looking to introduce a test driven design style to writing my stored procedures for a postgresql database implementation. I have seen that pgTap is a popular unit testing tool for postgres and ...
4
votes
1answer
51 views

Create WAL at interval, but only if data changed

I have a PostgreSQL 8.4 installation, and I have configured the archive_timeout setting to 5 minutes to improve the odds that I always have complete WAL file to apply in case of system failure. ...
4
votes
0answers
63 views

How to analyze postgres client process/session memory

Is there a way to check how much of process memory is allocated for the specific buffer for a running postgres session (work memory, temp buffers, plan cache etc ... )? I'm asking because i want to ...
4
votes
2answers
3k views

Postgres cost of large volume of inserts in many tables in a single transaction

What is the additional cost incurred by inserting large volume or rows (millions), in many tables in a single transaction ? Can something be done (tuning parameters) so that the cost of inserting in ...
4
votes
1answer
1k views

Slow postgres query that uses quick sort instead of hash-aggregate

I'm on postgres 9.5 and working with a web-analytics database that records visitor traffic. I'm trying to optimize a very slow query gives me a count of the unique people that visited a given page ...
4
votes
1answer
1k views

Inserting in PostgreSQL Foreign Table Violates Primary Key Constraint10

I have two local databases in one of them I have created a foreign table that is referencing a table in the second database. When I want to insert in foreign table it inserts the primary key all over ...
4
votes
1answer
636 views

How to run pg_dump so that it doesn't slow down other connections?

I am running a daily pg_dump on PostgreSQL 9.3.14 database of approx. 50 GB of size: pg_dump -Fc dbname > file The problem I am having is that while pg_dump runs, which takes several hours, all ...
4
votes
0answers
723 views

How can I determine why a concurrent Postgres index was marked as INVALID?

I'm trying to create an index concurrently. CREATE INDEX CONCURRENTLY blocks_expr_idx6 ON blocks USING btree ('blah') WHERE "type" = 'user root'; 12 hours after I ran the query, if I run \d blocks ...
4
votes
1answer
495 views

Postgresql: How to avoid encoding issues when copying a schema from one server to another?

I am using pg_dump and pg_restore to move a schema from one Postgresql 9.5 server to another. On the destination server: $ pg_dump -h source.example.com -n my_schema -v --no-owner -F c -f my_schema....
4
votes
0answers
362 views

PostgreSQL full text search: How to change what classifies as a “word separator”?

Using ts_debug I can see what symbols Postgres treats as (what I call) a "word separator". Example: SELECT ts_debug('english', 'Hello. ABC') Result: (asciiword,"Word, all ASCII",Hello,{...
4
votes
1answer
560 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 ...
4
votes
0answers
1k views

Moving extensions in AWS RDS (Postgres)

Initially we installed all extensions in default "public" schema. Now we have more and more other schemas - thus we want to add extensions and common functions to the schema common. Unfortunately AWS ...
4
votes
0answers
997 views

Preventing Infinite Trigger Loops in PostgreSQL

I am working with two PostgreSQL databases (one internal and one on our company Web server) where I am attempting to share data between the two instances through the use of triggers. The databases ...
4
votes
1answer
212 views

Pagination - Text comparison with greater than and less than with DESC

I am implementing a seek method for pagination and am wondering about how to best query on a text column with DESC. The queries for this seek approach use a less than or greater than depending on if ...
4
votes
0answers
182 views

PostgreSQL/PostGIS: Query seems inordinately time-consuming

I'm doing what I would consider a pretty straightforward query on two tables that aren't huge (~626k records in one; ~47k records in the other). Both tables have GIST indices on their spatial ...
4
votes
0answers
3k views

Postgres Streaming Replication: How to re-sync data in master (with standby) after failover

I am absolutely new to postgres 9.1 replication mechanisms. I have managed to set up streaming replication between a master and standby postgres (Both have x number of records). At one point when ...
4
votes
0answers
1k views

Ways to process XML into a relational structure in Postgres

I have a database that is comprised of ~10 tables, most of which have many-to-many relationships with each other (and an associated join table). I am inserting of the order 100k XML files into a raw ...
4
votes
1answer
269 views

Guidelines on best indexing strategy for varying searches on 20+ columns

I’m running a search engine for cars. It’s backed by a postgresql 9.3 installation. Now I’m unsure about the best approach/strategy on doing index optimization for the fronted search. The problem: ...
4
votes
2answers
4k views

How to configure replication from PostgreSQL (RDS) to vanilla PostgreSQL?

I have an Amazon RDS Postgres DB and I created a "replicator" user and granted it the "replication" privilege. db=> create role replicator login password 'something'; CREATE ROLE db=> alter ...
4
votes
0answers
5k views

Achieve PostgreSQL hstore functionality in MySQL

Please suggest any MySQL 5.5+ Engine that can support collection of key-value pairs be stored in any column of a table. I don't know if this feature is available out of the box in MySQL like ...
4
votes
0answers
179 views

Postgres TupleTable like data structure

I'm writing my own Group by operator (non-hashed implementation) and I'm currently looking on a good data structure to store the result groups. There are two requirement in my mind: the table need to ...
4
votes
0answers
952 views

How to properly configure pgBouncer transaction level pooling?

What is a good way to determine the number of available transaction connections when using pgBouncer to pool database connections? Also, how should I determine the number of max_connections and ...
4
votes
0answers
2k views

Can PostgreSQL's COPY create CSV with Locale-specific number formatting?

I want to create a CSV with PostgreSQL 9.1 COPY that directly opens correctly with a double-click in crazy German Excel 2010. Using ";"-Delimiter is easy. The encoding is solved, but Excel only ...
4
votes
1answer
140 views

Could a query be still active after pgAdmin4 crashed?

As i am asking in the title, I am wondering if a query can be still active after pgAdmin4 crashed ? Indeed I was running a very long-time query.. CREATE TABLE intersection as ( SELECT buff.rownum ...
3
votes
1answer
3k views

How to estimate reindexing time?

How do you estimate the total time reindexing an entire table will take in PostgreSQL? I have a large PostgreSQL database, the primary table of which has 15 million rows with 100 columns and 200 ...
3
votes
0answers
31 views

How to optimize this recursive postgresql query?

I've been searching a lot on this topic but I'm stuck at optimizing a query. I've created a complete dbfiddle to see the problem, if you increase the amount of inserts for the comments table you'll ...
3
votes
0answers
155 views

Restoring from PostgreSQL 9.5 dump to 10 is super slow

I am trying to restore database dump created on PostgreSQL 9.5.16 to PostgreSQL 10.7 and it is taking a lot of time to restore. Restoring the same dump to 9.5 doesn't take that much. So I am wondering ...
3
votes
0answers
65 views

TCP Resets when using psycopg2

I'm trying to do a simple health check for keepalived and other services via a python script and psycopg2 library. All seems to be alright, until I close the connection, at which point a packet with ...