Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

Filter by
Sorted by
Tagged with
2
votes
1answer
25 views

Simple select query hangs

I have a simple query: SELECT a.name AS a_name, b.description AS b_description FROM a JOIN b ON a.id = b.a_id WHERE b.table_name IS NULL; (Names changed to protect the innocent) ...
1
vote
1answer
20 views

Remove the deeper jsonb element in Postgres

[ { "type":"test.added", "values":[ { "field":"field1", "value":"fieldValue1" }, { "field":"field2", "...
0
votes
2answers
38 views

Is it Possible To Take Full And Differential Backup in Postgresql Like Sql Server?

I have a postgresql server. I want to take its backup full and differential mode as it is like in sql server. Is it possible to do this in postgresql? How can i do it, if it is?
0
votes
2answers
32 views

Missing data for one schema in pg_dump

I have a Postgres 9.6 database with several schemas, and a regular backup of this database is done with a command like pg_dump -h localhost -d db -U postgres -Fc -f file.dump When I restored a ...
0
votes
0answers
9 views

postgres 9.6 - use data from xml to call procedure

I work with a system based on Postgres 9.6 (updated from 9.1 or something similar). One of latest changes requires an ability to remotely call procedure on database with some arguments. Due to various ...
0
votes
1answer
72 views

PostgreSQL 9.6 pg_rewind - takes a long time to replicate

I use Streaming Replication,and Replication works normally. Two servers are HA and PostgreSQL DB is replicated. The data in the DB is about 40G. When a failover occurs, the slave is successfully ...
1
vote
1answer
16 views

How to block pg_stat_reset() statement in DB

Team, Recently we have found one of our application is triggering pg_stat_reset() executing everyday which is causing performance issue . to disable this statement thru application level will take ...
0
votes
1answer
40 views

Multiple concurrent “REFRESH MATERIALIZED VIEW”: how to manage?

I have a large Postgres database with many tables, some with tens of millions of rows. Several working processes update the database concurrently. For faster searches, relevant data is compiled into a ...
0
votes
1answer
12 views

Is it possible to make simple postgres dump using replication server?

PostgreSQL 9.6, configured backup server via barman tool. Using WAL streaming (pg_receivexlog) and streaming backup (pg_basebackup). All working good. However for development/debugging purposes I may ...
0
votes
1answer
17 views

group several columns together in materialized view

For statistical reasons, I need to group several ids in a single column of a materialized view. This should form a list of ids from other tables with an info. Further processing will be done on this ...
0
votes
0answers
27 views

Users mostly care about recently inserted rows. But the table has a high number of rows. What is the best way to improve performance?

I have a huge table with rows > 1000000. However, people will only care about rows that were ordered by updated_at and created recently. There are updated_at and created_at columns in the table. By ...
0
votes
0answers
35 views

How to create multiple pool connection using Psycopg2 in Cloud Functions (GCP)

I am creating Cloud Functions for some queries to read and write in different databases. In GCP concept, I am connected to Cloud SQL - Postgres and using Psycopg2, tested locally - python postgres ...
0
votes
0answers
32 views

AWS Aurora Postgres 9.6 major version upgrade via slony?

The official documentation states that the major in-place version upgrades are not possible. Note Aurora PostgreSQL does not currently support in-place major version upgrades. To migrate a ...
0
votes
1answer
32 views

How to loop through JSON array of JSON objects to see if it contains a value that I am looking for in postgres?

Here is an example of the json object rawJSON = [ {"a":0, "b":7}, {"a":1, "b":8}, {"a":2, "b":9} ] And I have a table that essentially looks like this. demo Table id | ...(other columns)...
2
votes
2answers
36 views

Postgresql expanding view multiple times instead of executing it once

I have a rahter long subquery which I store as a view CREATE VIEW scans_minmax AS SELECT tmp.source, value ->> 'Ok' AS "Ok", ...
1
vote
1answer
50 views

How to take backup of multiple DB in a specific point of time

My test setup is having 3 databases in postgresql-9.6 instance, 2 DBs consumed by 2 live Web apps. I am using pg_dump to take hot backup of all databases. In order to keep CPU load under threshold, ...
5
votes
1answer
51 views

What accounts for a large single index size (other than bloat?)

I've been doing some digging into the size of our production PostgreSQL 9.6 database and found some results I thought were surprising. We have a table (let's call it foos) with about 10 million ...
-1
votes
1answer
18 views

open cursor failing

I am using embedded SQL (.pgc files) of PostgreSQL and a cursor to retrieve from a table EXEC SQL DECLARE mycur CURSOR FOR select col1 from table limit 10; if(sqlca.sqlcode != 0) { os &...
1
vote
1answer
27 views

Improve Performance by Left Join and Group By Count - Postgresql

I am trying to write a query that does COUNT(*) on GROUP BY and a LEFT JOIN. How do I improve the left join? I am using the below index create index session_opportunity_index on public.session ...
0
votes
1answer
27 views

Is it possible that information_schema.columns doesn't hold an user table column information?

I am facing a strange issue here. I have a table which is not visible in information_schema.columns view. What could be the reason for that. Note: I am using RDS Aurora Postgresql 9.6.12
0
votes
2answers
45 views

Postgres using seq scan with filter on indexed column + EXISTS on related table

I've following query which takes about 25-35 seconds to execute: EXPLAIN (analyze, buffers, format text) SELECT booking.* FROM booking WHERE booking.reference_number = '9999999999' OR booking....
0
votes
1answer
16 views

How to find SSL root cert that made connection to the database in PostgreSQL?

When we connect to postgreSQL via ssl-mode=verify-full how will I make sure if the certificate I passed is used while making the connection? With ssl_is_used(); shows only true or false. Is there ...
1
vote
2answers
54 views

Restoring Postgres from only base backup taken with wal-e

I have a postgres base backup taken via wal-e. Is it possible to restore and start the DB with just the base backup? I cannot use the restore_command in recovery.conf since the wal files are archived ...
2
votes
1answer
33 views

Automatically reload DB nightly

Nightly we dump our Production DB and load it into our Staging and Dev environments. A bash script is taking care of that and the steps to achieve this are the following: download the dump from the ...
2
votes
3answers
146 views

Does Postgres automatically rotate WAL files out (pg_xlog) if archive_mode is off?

I'm relatively new to PostgreSQL administration and I'm attempting to pick up the nitty gritty details as I go. My question is, assuming I have no need for disaster recovery and I don't turn on ...
1
vote
1answer
23 views

PostgreSQL: joining on jsonb array

Using this contrived example structure: Base table data { "uid": "b12345", "nested": ["n12345", "n34567"] } Nested table data [ { "uid": "n12345", "message": "Hello world" }, { "uid": "...
2
votes
0answers
123 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 ...
1
vote
1answer
21 views

How safe are PostgreSQL CONCURRENTLY option

As I know CONCURRENTLY let DATABASE create an index without any lock though It could incur more CPU usage. I am planning to create an index in production DATABASE with the option. Before applying ...
3
votes
2answers
75 views

Is DEFAULT NULL and 'nullable value without default' completely the same?

ALTER TABLE test_table ADD COLUMN a DEFAULT NULL; vs. ALTER TABLE test_table ADD COLUMN a; Both columns will set NULL if column a is not specified. As far as I know, if I add a column into a table ...
0
votes
1answer
34 views

Performance issues with CAST function

Observing this performance issue that can't be explained with EXPLAIN in a PostgreSQL 9.6 database. There is currently a table public.users, which has a cast on it to output a record to JSONb. (e.g. ...
0
votes
0answers
15 views

how to restore wal file that is in tar format in postgresql

I am trying to use incremental backup provided by postgres. It is working if I copy the WAL files as it is in another folder and then recover. But if I make .tar.gz of WAL file then try to recover db ...
-1
votes
1answer
42 views

Postgres - Query for accounts with privileged access [closed]

Does anyone have a postgres query which shows all accounts with privileged permissions like admin/sa. Basically trying to identify any accounts members of roles that have elevated access for audit ...
0
votes
1answer
80 views

Why and When the pg_stat_activity's most of the columns are null?

Recently, I have noticed that when I query the pg_stat_activity for few users most of the columns are [null] (pgAdmin 4) in the result. Only thing I can see is datid, datname, pid, usesysid, usename,...
0
votes
1answer
53 views

Postgresql 9.6 HA issues

(Editing the question with additional information: ) We are in process of testing a Postgres 9.6 active-passive HA cluster using streaming replication on Rhel 7.5. The HA setup is done using ...
0
votes
1answer
21 views

UUID array declared in stored procedure, on access throws explicit type casts needed

I have a stored procedure where I have declared an array of UUIDs, on using that variable, it throws casting exception. create or replace function test.test_function(max integer, interval interval) ...
2
votes
3answers
136 views

Query to get counts of values per column

I have a big table of vendor-supplied data (that I can't change around much) with about 315 columns. I suspect that many of the columns are not being used (or at least not consistently). I'd like a ...
0
votes
2answers
35 views

How to do a cumulative count for certain values

I have a scenario to do cumulative counts in certain way. So I decided to create an experiment for that before I run the query in the actual environment. I have created a table as: create table ...
0
votes
2answers
84 views

How to use pg_archivecleanup on Windows

Trying to figure out a proper way to clean old WAL files located in archive directory for point in time recovery purposes. I know I can use hard-coded name of WAL segment something like this: ...
5
votes
3answers
210 views

Postgres sometimes uses inferior index for WHERE a IN (…) ORDER BY b LIMIT N

We have a PostgreSQL table with ~5 billion rows that has developed a nasty habit of missing the proper indices and doing a Primary Key scan on certain LIMIT operations. The problem generally ...
2
votes
2answers
37 views

Will our application server have issues connecting to a Postgres 11.4 server with a 9.6 client?

Our application server is on EC2 running on Amazon Linux 1. Postgres dropped support for Amazon Linux and so we depend on Amazon providing the postgres client. The client is 9.6 and our Amazon ...
0
votes
0answers
15 views

How to find out all the postgresql database cluster data directories (running and Shutdown instances) on linux server

Could you please help us to find below details. How to find out all the Postgresql database cluster data directories (running and Shutdown instances) on Linux server. Regards, Siva.
1
vote
1answer
108 views

Postgresql cannot restore partial pg_dump “relation ”public.my_table_seq“ does not exist”

In my database I dumped some tables via the following psql command: pg_dump -t my_table1 -t mytable2 > my_dump.sql And I try to import it into a postgresql docker image as a standalone database ...
1
vote
1answer
50 views

Speed up 180M+ rows returned by simple SELECT with PostgreSQL

I have performance issues on a SELECT statement which returns 180+ out of 220+ of millions of rows on my managed PostgreSQL. The table structure is the following: CREATE TABLE members ( id ...
1
vote
1answer
45 views

How to rotate pgstartup.log

My pgstartup.log has got big. I have renamed it, and can set up logrotate to do it automatically if need be, but of course the server is still writing to the open file. I'd rather not restart the ...
2
votes
0answers
37 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 ...
0
votes
1answer
504 views

pgAdmin4 or pgAdmin3? [closed]

I've seen a lot of hate directed to pgAdmin4, but honestly I can't see why. It worked out of the box from a docker image, connected right away to the database and perfectly managed every maintenance ...
1
vote
1answer
47 views

Connection to PostgreSQL disconnects every hour regardless of activity

I'm monitoring my PostgreSQL clients using a periodic netstat -an | grep 5432 | grep EST to check the ESTABLISHED status of the connection every 5 minutes. I have noticed that all of the connections ...
0
votes
0answers
136 views

PostgreSQL 10 logical Replication not working !!! slots blocking calls not sending wal events back to client

Postgres logical replication not working we have created logical replication slot on pg 10 using below command select * from pg_create_logical_replication_slot('slot', 'wal2json'); Above request ...
0
votes
0answers
65 views

How Insert data to child table in different schema using trigger function and auto updates for these child tables

I am trying to insert data to the child tables from different schema i.e parent table and trigger function were in schema 1 and child tables were in schema 2. It throws me an error while I am doing ...
1
vote
1answer
31 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 ...