Stack Exchange Network

Stack Exchange network consists of 175 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

Questions tagged [postgresql]

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

8
votes
0answers
429 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
536 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
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 ...
6
votes
0answers
368 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
83 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 ...
5
votes
0answers
2k 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
933 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
751 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
81 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
54 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
3k 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
925 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
481 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
306 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

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
393 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
929 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
174 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
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' ...
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
0answers
260 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
0answers
926 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
168 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
25 views

How are Transaction IDs compared in Postgres?

I'm trying to understand how Transaction IDs are compared in Postgres I have a two questions Transaction IDs (XIDs) are represented in 32 bits. Therefore there can be a maximum of 4,294,967,296 XIDs....
3
votes
0answers
49 views
+100

Update column without dropping dependancies

I have a table with many dependancies such as: functions, views and materialised views. I'd like to update a column from an int to a bigint. The issue I have is that doing so requires dropping a ...
3
votes
0answers
155 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
506 views

Postgres: permission denied for relation for read_only user

I am trying to set up a new read only user in postgres. I did the following steps to create the new user: sudo -u postgres createuser --interactive name: read_only_user superuser: n ...
3
votes
0answers
74 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
58 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
343 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
195 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
296 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
566 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
273 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
332 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
310 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
134 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
280 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
427 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
353 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
228 views

Is it possible to create a user defined type with plpgsql or SQL?

Distinct from a DOMAIN I was wanting to know if it was possible to create a user-defined type without C. Specifically I wanted to create a type like this CREATE TYPE name ( INPUT = ...
3
votes
0answers
34 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
583 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
330 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
461 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
670 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 ...