Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

136 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
8
votes
0answers
793 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
262 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
4k 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
632 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
278 views

How do I fix multixact wraparound after it has already occurred?

I have a database which has a single table for which MultiXact wraparound has already occurred. Neither manual vacuums nor automatic vacuums are able to fix this condition. Manual vacuums fail almost ...
3
votes
1answer
2k 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 ...
3
votes
0answers
2k 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....
3
votes
0answers
2k 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
2k 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 ...
3
votes
0answers
1k 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
586 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
13 views

Move Our Google Cloud SQL Instance to Compute Engine

We are running several Cloud SQL Instances on GCP and plan to move all of them to some Compute Engine instances, depending on the applications that use it. Our main concern is cost of the server. We ...
2
votes
1answer
13 views

Postgresql 9.6, create a user with only select permissions for a given database/schema

I want to create a user that has: has its default privileges revoked when I create it can only perform selects against a given database/schema Is this possible?
2
votes
0answers
21 views

How to fetch related rows on multiple condition with ranking

I have following table for storing item information in PostgreSQL. id | name | category | seller | m_age | status | type_id | region | ----------------------------------------------------------...
2
votes
0answers
55 views

CREATE TABLE IF NOT EXISTS fails with privilege exception when table exists

I discovered that CREATE TABLE IF NOT EXISTS will fail with insufficient_privilege if the role executing the statement does, in fact, not have the necessary privileges, but does so regardless of ...
2
votes
0answers
84 views

RAISE NOTICE doesn't work as expected while using within Dynamic SQL block

I have a small piece of code to get all sequences and the current and maximum value of all sequences in a database. I have tried with recursive and some other alternatives through plain sqls, however ...
2
votes
0answers
60 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
0answers
42 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
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: ...
2
votes
0answers
405 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
584 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
675 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
395 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
310 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
0answers
31 views

Create a Postgresql trigger that updates column with PK of other table

Hello and thanks for taking the time in advance to look at my question. This is my first try at using triggers and functions in PostgreSQL (using DBeaver) and I appreciate your feedback. I'd like to ...
1
vote
0answers
12 views

Is there any way to check WHERE clause stats on table

Not sure whether this is a valid ask or not, I'm trying to get the stats of the columns which are using in user query/ETL functions for the where clause. Can we check on a table, which column has ...
1
vote
0answers
19 views

Postgres Major version upgrade 9.4 -> 9.6. PITR compatibility across versions?

Just dug into postgres documentation and couldn't get a clear answer on this. With Debian Jessie hitting EOL for Debian LTS in June, we are upgrading our database servers along with our other servers ...
1
vote
0answers
38 views

Difficulty Optimizing Postgres Write Throughput

I'm trying to index a collection of roughly 127K files using a PostgreSQL 9.6 server on RDS. I had expected the process of writing these documents to the DB to take about 8 hours, but over time I ...
1
vote
1answer
34 views

Remove the deeper jsonb element in Postgres

[ { "type":"test.added", "values":[ { "field":"field1", "value":"fieldValue1" }, { "field":"field2", "...
1
vote
1answer
49 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
0answers
118 views

Physical and logical replication slots on Postgres (same time)

I wonder if it's possible to have a setup when changes from a single Postgres master streamed to two (or more) replicas same time, but some replicas to use streaming (logical) replication and some to ...
1
vote
0answers
85 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
52 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
20 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
0answers
623 views

PostgreSQL Optimize bulk values CTE and SELECT INTO TEMP TABLE to prevent OOM kills

Recently we moved some code from Rails way to raw SQL for performance reasons. We have some jobs which fetch some data from APIs, data can be in 100K+ rows of count sometimes. This data needs to be ...
1
vote
0answers
39 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
49 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
39 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
52 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
176 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
229 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
685 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
760 views

How to delete orphan temp tables in Postgresql 9.6

I noticed in my log a few entries about autovacuum: found orphan temp table "pg_temp_35"."sometable" in database "mydb" I tried to drop them but got: (could not Retrieve the result : ERROR: could ...
1
vote
0answers
18 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
34 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
295 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
832 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
210 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
2k 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
67 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 ...