Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Make your voice heard. Take the 2019 Developer Survey now

Questions tagged [postgresql]

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

8
votes
0answers
358 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
0answers
44 views

Postgres: query planner not respecting table inheritance constraint when querying for null

I'm having an issue with slow queries that is caused by the planner checking every inherited table instead of only the one with the constraint. I have a table with 0 rows called "search_result". This ...
6
votes
0answers
479 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 ...
6
votes
0answers
353 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
0answers
767 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
6k views

What are the compression options for storing large amounts of text in Postgres?

I need to store large amounts of text in Postgres, mainly command logs and the output of long running commands and need to know if there are some settings that could help compress the data ...
5
votes
0answers
693 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 * .... * ...
4
votes
0answers
53 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 ...
4
votes
0answers
60 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
0answers
46 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
0answers
2k views

AWS RDS Postgres Logical replication

I am using AWS RDS Postgres's and RDS's logical replication support (Edit: Note, this is not the same as the logical replication featurein PostgreSQL 10. It's AWS RDS functionality based on the same ...
4
votes
0answers
896 views

How exactly does one configure master-slave replication for PostgreSQL 9.6?

I have configured master node the way you would configure it on 9.4, but I am getting this error on the slave node: WARNING: pg_stop_backup still waiting for all required WAL segments to be archived ...
4
votes
0answers
420 views

Postgres pgAdmin4 and table maintenance fails

I just loaded Postgres 9.6 for a dev db and using pgAdmin4 I cannot run any table maintenance at all. I added the path in the binary and it continues to fail. Please help as I have no idea how to ...
4
votes
0answers
11k views

How do I install/enable the uuid-ossp extension on Postgres 9.3?

I’m using Postgres 9.3 on Ubuntu Linux 14.04. How do I install/enable the uuid-ossp extension? I first verified the contrib package was installed … root@prodbox:/home/rails/myproject# apt-get ...
4
votes
0answers
270 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
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 ...
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
342 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 ...
4
votes
0answers
870 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
0answers
429 views

Replication of specific tables using Postgres 9.4.4 and Amazon Web Services

I have currently setup a Postgres 9.4.4 RDS instance on Amazon Web Services. On this instance I hold 20 tables. In addition I also have Postgres 9.4.4 installed on my local server (OS: 64-bit Linux) ...
4
votes
0answers
168 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
948 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
0answers
974 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
899 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
166 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
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 ...
3
votes
0answers
40 views

PostgreSQL Enum : Search on ENUM type

Defined an ENUM CREATE TYPE currency AS ENUM('GBP', 'EUR', 'USD'); Using this as a type in a table CREATE TABLE if not exists transaction( id BIGSERIAL NOT NULL PRIMARY KEY , amount NUMERIC(35,...
3
votes
0answers
46 views

Bloat on Materialized Views

The Postgres documentation says that materialized views with concurrent refreshes generate bloat as usual tables, because it executes deletes and inserts. For usual tables, there are some queries to ...
3
votes
0answers
56 views

How to efficiently store 672 bits of information in PostgreSQL

I have a requirement that needs to store lots of bits and it's actually a time setting for every 15 minutes in a week (indicating if a user is free during that time). So for one day we need: 60 ...
3
votes
0answers
164 views

Setup a PostgreSQL HA with synchronous replication in windows

I am fairly new to postgres ans would like recommendations for setting up HA system in windows Server machines. My current plan is to run 2 server Primary (Data center) with postgres 9.6 and same ...
3
votes
0answers
82 views

Postgres 11 - Fetching results after a transactiom

With the recent update of Postgres 11. It now supports procedures. Transactions are finally supported in stored procedures in Postgres. However, I have been trying to perform a transaction and ...
3
votes
0answers
234 views

What can cause slow planning times for “select 1”?

I have a database that is used by a Production application. It's an db.m3.medium on Amazon RDS. We've noticed that a lot of queries are starting to get slow that typically shouldn't be slow (e.g. ...
3
votes
0answers
413 views

Npgsql Timeout for simple query when run in parallel

I'm struggling to determine the cause of an exception when the code is run in parallel, but works fine when run one at a time. As background information, I'm running a set of unit tests, where each ...
3
votes
0answers
238 views

Best way to cast array (text[]) to record-type?

PostgreSQL has the ability to create record types. A lot of operations like regexp_match return text[]. What is the most efficent way to go from text[] to a custom record type? Currently, I'm doing ...
3
votes
0answers
260 views

Postgresql update performance?

We have a table which stores about 20 million records: Table "public.tbl" Column | Type | Modifiers -------------------------+------------...
3
votes
0answers
1k views

PostgreSQL High Disk I/O

I am evaluating PostgreSQL as an alternative to Oracle. I have a database with 533 Tables which contain up to 250,000 entries. For a performance comparison, I built the datbase both on Oracle and ...
3
votes
0answers
268 views

Spike in Disk I/O due to external process causing PostgreSQL commits to pile up in the slow log

Background: I have a live Django app that utilizes 4 Redis instances alongwith PostgreSQL 9.6.5. Note that the first two Redis instances are big in size: back ups amount to ~2GB and ~4.4GB ...
3
votes
0answers
1k views

PostgreSQL slower query after partitioning

I have a 75GB table with more than 100M records. I've partitioned that table by month. Each partition is ~8M records big. Most of the queries works on recent data and almost every query filters by ...
3
votes
0answers
112 views

Corrupted tables in postgresql: invalid relnatts, attrelid and typrelid

When trying to upgrade my databases from Postgresql 9.5.4 to 9.6.3, two tables are corrupted. $ /usr/local/Cellar/postgresql/9.5.4_1/bin/pg_catcheck -h localhost -p 5434 db_concepts_2 notice: ...
3
votes
0answers
219 views

SELECT query blocking writes in postgresql

We have a web application that sends emails with a queue worker. Before an email is sent we write to a table: INSERT INTO user_email (user_id, email_key, sent_at) VALUES ( 175413579283955991, ...
3
votes
0answers
368 views

Which SQL Power Architect type to specify for [PostgreSQL] UUIDs/GUIDs in new column property template/UI?

I have inherited maintaining and extending SQL Power Architect projects in source control which are used to generate the DDL for project DB schemas. We are currently using PostgreSQL 9.5 and SQL Power ...
3
votes
0answers
321 views

Setup to allow creating new tables in PostgreSQL replica

My goal is to create a replica of an existing database that would allow me to create new tables and populate them. I don't care about writes to replicated tables - those can and probably should throw ...
3
votes
0answers
32 views

Upgrade PostgreSQL 9.3 without moving the DB and messing PostGIS on OSX OpenGeo Suite original installation

I looked for the answer, but could not find a specifically fitting one, so would appreciate an answer or a pointer to one... Q: How do I upgrade PostgreSQL 9.3 to the current (now 9.6 ?) without ...
3
votes
0answers
532 views

Problem: Remote connection with pgAdmin4

I just installed the Java and PostgreSQL environment on a server (for remote testing) CentOS 7 From the command line using ssh would seem that there are no problems in the sense that I can access the ...
3
votes
0answers
281 views

FTS non-latin: text-search query contains only stop words or doesn't contain lexemes, ignored

I'm trying to use Full Text Search with Chinese characters using a postgres (9.6.1) FTS extension ZHParser. Following some of the examples here, I wanted to select my title column and match the title ...
3
votes
0answers
332 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 ...
3
votes
0answers
568 views

Does PostgreSQL support `unnest` as a column?

Does PostgreSQL document and support this behavior of the SRF unnest() as a computer/virtual column, SELECT unnest(array_agg(id)) AS id FROM foo GROUP BY CUBE (a,b,c,d) HAVING count(*) > 1 If ...
3
votes
0answers
36 views

Postgres query changes and degrades radically

I have a hibernate-based query that is not performing well. It is running a query that joins three tables - asset, asset_type, and asset_status - and sorts the results, and running the query on a ...
3
votes
0answers
266 views

Amount by which to increase checkpoint_segments (postgresql)

In my postgresql log, I see statements such as checkpoints are occurring too frequently (26 seconds apart). Currently in the conf, checkpoint_segments are 32, checkpoint_timeout is 15 mins and ...