Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

120 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
8
votes
0answers
497 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 ...
5
votes
0answers
203 views

UUID V1 and V4 bloats substantially after sequntial inserts and deletes. Why is it so, compared to serial and temporal BTree (which does not bloat)?

I am trying to understand the impact of deletions to bloat of tables and indexes after large number of inserts and deletes. The inserts and deletes are following a rather strict pattern: first the ...
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 ...
3
votes
0answers
839 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
2answers
476 views

self-joins in recursive CTE: `recursive reference must not appear more than once`?

This is tested with PostgreSQL 9.6, but it's a general SQL question. Is it possible to use self-joins of the recursive table in a recursive CTE (rCTE)? I tried the following rCTE containing a self-...
3
votes
0answers
957 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 ...
3
votes
0answers
528 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 ...
2
votes
0answers
29 views

Left join with generate_series misleads the planner

Using postgres 9.6, i'm currently managing time-based data with the following structure: Column | Type | Collation | Nullable | Default ----------------+-----...
2
votes
1answer
164 views

PostgreSQL most efficient way to reference multiple tables

I am looking for the most efficient way to reference multiple tables in one table, when there is only one reference possible at a time. Which means tables A and B are referenced by table C, but both A ...
2
votes
0answers
36 views

PostgreSQL: foreign table with comments

When creating a foreign table in PostgreSQL 9.6, comments (COMMENT ON smth IS "Text.") are not copied from the source table/view to the destination foreign table. How do I import the comments, ...
2
votes
0answers
1k views

Downgrade working postgres cluster 10 to 9.x

Is there anyway to downgrade working postgres cluster 10 to 9.6? There are major changes (i.e, pg_wal / pg_xlog, etc.,) been done in 10. So far, I decided to take dump and restore into new 9.x cluster....
2
votes
0answers
348 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 ...
2
votes
0answers
1k views

PgBouncer cannot connect to server

I have configured PgBouncer with PostgreSQL 9.6 on windows 7 machine. Here are the pgbouncer configuration settings; [databases] test = host=127.0.0.1 port=5432 dbname=master_db user=postgres ...
2
votes
0answers
400 views

PostgreSQL: Slow Update Query

I'm running this update script in PostgreSQL 9.6: do $$ begin create temporary table if not exists tmp_rutting1 ( road_id integer, direction_id integer, begin_km double ...
2
votes
0answers
838 views

Track my own custom metadata on Postgres tables and columns

Is there any way to add my own metadata to tables and columns in Postgres, such as key-value pairs of short text? I am migrating tables, columns, and data from another database system. That other ...
2
votes
0answers
525 views

Postgresql 9.6 autovacuum blocks all update operations

I have a table with following definition: CREATE TABLE public.json_stream_packet_filter ( stream_packet_id bigint NOT NULL, filter_data jsonb, CONSTRAINT json_stream_packet_filter_pkey PRIMARY ...
2
votes
0answers
313 views

Pgpool II and Postgresql upgrade scenario using different major versions

Pgpool FAQ states that: You cannot mix different major versions of PostgreSQL, for example 8.4.x and 9.0.x And the reason that I understand very well is: Pgpool-II assumes messages from ...
2
votes
0answers
243 views

How to detect if a postgresql gin index is bloated

For btree indices, the pgstattuple extension provides the avg_leaf_density in pgstatindex to assess the 'bloatedness' of an index, i.e. the approximate ratio of the current size and the size it would ...
1
vote
1answer
10 views

Update all the elements of JSON array nested in a JSON column (Postgres)

I have json column with data that looks like this: { a: [ { q: 5 }, { q: 8 }, { q: 10 } ] } I'd like to map over the elements in the array and add an extra property ...
1
vote
1answer
41 views

Optimize Postgres query where a time range as well as IN values

I have a table that gets around 20 million new records a day for positions of vessels. I want to query for a couple of different vessels and get the records for any period. As I'm using this for ...
1
vote
0answers
27 views

Dumping Postgres schema from Google cloud SQL

Now this may look trivial but it is a managed service so I don't have the superuser rights. And various schema/tables/sequences have different roles/users as their owners so I can't just use pg_dump ...
1
vote
0answers
49 views

Validation within a transaction with all violations in response

I'm trying to solve a problem with some sort of offline integration where data occasionally (once in several days) comes in a form of csv files. It can be new rows or updates for existing ones. Total ...
1
vote
1answer
15 views

Wraparound Transaction ID

Once the txid has passed the the xmin/xmax 32 bit limit, and assuming you've not suffered any issues with wraparound because you've been correctly maintaining your tables (plenty of vacuums, vacuum ...
1
vote
1answer
674 views

Relation does not exist while scanning schema

My Postgres database is getting slow while executing some function. But another database of identical design and with more data is much faster than the first one. I have recently migrated data from MS ...
1
vote
0answers
20 views

PostgreSQL database for GITLAB - Grant privileges only to user PUBLIC?

I manage a Postgresql database for a GITLAB application. The developer provided me with a backup file for migrating his database to a new server that contains the following statements: ALTER DEFAULT ...
1
vote
0answers
12 views

Python transfer all data of table to table of different database

I can connect to a database Postgres with psycopg2, but this just 1 database. I have a problem that use Python code to move data of this table (database No.1) to another table (database No.2). The ...
1
vote
0answers
30 views

Exclusive and Inclusive ranking queries in Postgres 9.6

I'm trying to build a filtering/ranking search that would let me order and optionally filter out people based on a 1-to-many relationship with job "focuses". The tables are shown at the bottom. A "...
1
vote
0answers
46 views

Create a postgres user role that can't access a specific table

I have a postgres 9.6 database with many tables. I need to create a role that doesn't have access to one of these tables. I think I need to do a Grant Permissions, but I haven't been able to find ...
1
vote
0answers
116 views

Does barman require continuous execution of receive-wal on a slot for streaming replication to work?

I am new to barman and am trying to implement barman on my server. I have followed each and every step as mentioned in the barman docs. Everything works fine till the receive-wal command in running in ...
1
vote
0answers
182 views

Import to clean postgres database incomplete

I have been trying to copy a postgresql database to my local machine to use for some testing. I first generated a dump using pg_dump -h myhost -U myuser -f mydump.sql mydatabasename and then tried to ...
1
vote
0answers
385 views

Postgresql Crashed after os reboot

In Postgresql 9.6 after reboot first I got: 2018-04-21 14:36:27 UTC LOG: database system was shut down at 2018-04-21 11:01:15 UTC 2018-04-21 14:36:27 UTC LOG: invalid checkpoint record 2018-04-21 ...
1
vote
0answers
17 views

Conditionally throwing based on pre-fetched result stored in a temp table inside a function

I need to apply some validation rules on set of a data I've fetched for a given account in order of using postgres v9.6: Fetch all rows applicable to account. For each validation rule, query subset ...
1
vote
0answers
27 views

Identify duplicate IDs in partitioned table results in out of memory error

I try to run the following query to identify duplicate IDs in a partitioned table (2432 partitions, all more or less 221000 rows). I created the partitions based on a geometry, not Range or List. The ...
1
vote
0answers
145 views

How does Postgresql determine sort keys into the sort node?

This is the situation: I'm using Postgres 9.6 and had to migrate the database to a new server. This database is quite small, about 18MB in 25 tables but most of the records must be pulled in one query ...
1
vote
0answers
566 views

Default privileges for object owner

We have doubts regarding default privileges for owner of objects. Let me show by example: We start by creating a brand new role and schema: psql (9.6.2) Type "help" for help. postgres=# CREATE ROLE ...
1
vote
0answers
179 views

AWS RDS PostgreSQL Storage

One of my AWS RDS PostgreSQL instance is consuming too much storage even though there is no activity going on the Database. I have logical_replication switched ON, to use AWS DMS to replicate DBs to ...
1
vote
1answer
1k views

PostgreSQL database size doesn't match used space on disk

I have a PostgreSQL 9.6 database with high-volume traffic. I run periodically pg_repack reclaim unused space in tables/indexes. On larger tables repack sometimes fails to complete the process which ...
1
vote
0answers
55 views

Optimising group by on self referential join with millions of rows

I have a table (actually materialized view) with millions of rows. Schema is something like: id correlation_id name I want to be able to determine, which ids appear together, and how many times. For ...
1
vote
0answers
2k views

How to write a comma-separated list of a single scalar variable in PL/pgSQL?

This is related to: Postgres function assign query results to multiple variables https://stackoverflow.com/q/15949337/5419599 I want to loop over values returned in a single column. In other words: ...
1
vote
0answers
622 views

UUID default value on PSQL

I'm creating my database tables inside a flask application via SQLAlchemy. According to this answer, my code should look like this: id = db.Column(db.CHAR(36), primary_key=True, server_default=str("...
1
vote
1answer
84 views

Slow PostgreSQL 9.6.5 query with LIMIT, but only for tsquery with no results

Table a has > 3 million rows and this index: CREATE INDEX idx_a_txt ON a USING gin (txt); This query takes 5 seconds if I search for something that doesn't exist, and 31 milliseconds if I ...
1
vote
0answers
36 views

Insert Object Reference Into Table

I'm currently learning about object-relational databases and decided to whip up an example in PostgreSQL to test what I'm learning. Here is the example I wrote up for testing: Create Type item_type ...
1
vote
0answers
52 views

postgresql delayed utl_file writes

I'm trying to understand what postgresql doing in an issue that I'm having. Our app team wrote a function that runs with a cursor over the results of a query and via the utl_file func they write some ...
1
vote
0answers
193 views

Problem creating extension pg-strom

I'm using PostgreSQL 9.6 on Leap 42.3. I've compiled and installed pg-strom. Everything seems to work fine: LOG: PG-Strom version 2.0devel built for PostgreSQL 9.6 LOG: PG-Strom: GPU0 GeForce GTX ...
1
vote
0answers
972 views

Migration from postgres 9.4 to postgres 9.6

I did the migration from PostgreSQL 9.4 to version 9.6, but now the stream replication is not working, it stopped replicating. Script followed: installation of postgres 9.6 on the three servers (...
1
vote
0answers
401 views

Permanently disable OOM Killer for Postgres

Following this documentation, I am trying to permanently disable OOM Killer for Postgres 9.6.2 on Ubuntu 14.04.5 LTS. In /etc/init.d/postgresql, I add: echo -1000 > /proc/self/oom_score_adj In /...
1
vote
0answers
32 views

Can PostgreSQL data files be moved from macOS to Fedora?

Can I copy the PostgreSQL data directory on a macOS (10.12.3) machine to a Fedora (25) machine then expect PostgreSQL to work on the destination? I specifically want to avoid pg_dump/pg_restore ...
1
vote
0answers
43 views

What exactly does 'localtime' mean as value of configuration parameter timezone?

On PG 9.6, when I do SHOW timezone;, I get: TimeZone ----------- localtime (1 row) which appears also in postgresql.conf as the value of configuration parameter timezone: timezone = 'localtime' ...
1
vote
0answers
802 views

How to avoid downtime during postgresql upgrade

Im having a production PostgreSQL server 9.2, it has a few databases around 150GB. I want to upgrade this to 9.6, so I decided to upgrade this on QA server. I have installed PostgreSQL 9.6. Then ...