Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

Filter by
Sorted by
Tagged with
0
votes
0answers
8 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 ...
0
votes
0answers
32 views

Best way to update bulk row in production database [closed]

I want to update a column that has 10 million rows in a production Database. Surely I think that just running an UPDATE command with that much row would lead some trouble because of TABLE LOCK and ...
-1
votes
1answer
38 views

Postgres - Query for accounts with privileged access

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
18 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
44 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
14 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
115 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
32 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
41 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: ...
4
votes
3answers
170 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
32 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
12 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
21 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
44 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
0answers
20 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
26 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
62 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
36 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
48 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
58 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
20 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 ...
0
votes
1answer
21 views

Postgres VACUUM taking forever

I have an specific table that is taking forever o VACUUM. There are no locks in the DB and even letting it overnight the VACUUM didn't finish. According to the statistics (ANALYZE works fine) the ...
0
votes
0answers
70 views

Unit postgresql.service failed to load: No such file or directory

When i try to connect to my postgres database, i get the error below: could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "10.2.1.96" and accepting ...
0
votes
1answer
22 views

How SQL query are evaluated

I ran into an issue on trying to optimise the following two query into 1 single query. Result 1: select * from sip_trunks where resporg_account_id_id=62 and identifier = (select action ...
1
vote
1answer
42 views

Why disable Postgres Autovacuum?

I've got a task to work with a 9.6 Postgres DB, and for some reason Autovacuum is disabled. The database was extremely slow, and after a manual VACUUM it could breathe again. The point is, why ...
0
votes
0answers
51 views

Partitions for table that hold only ids in PostgreSQL 9.6

I am working in query tuning and decided to go for partitioning my tables. There are 3 tables in the base query and only one table makes the query slower. I tried to create partitions but that table ...
0
votes
0answers
46 views

PostgreSQL does not starting up after power failure

Could not start PostgreSQL after power failure: FATAL: could not find tuple for opclass 1 PostgreSQL version: root@ubuntu:/usr/lib/postgresql/9.6/bin# ./postgres -V postgres (PostgreSQL) 9.6.1 OS ...
4
votes
1answer
297 views

Try catch equivalent in Postgres

Do we have a try catch equivalent in Postgres? I have written some user defined functions that are called by trigger. I (don't) want to ignore errors so that flow does not get interrupted.
0
votes
0answers
41 views

Difference in DELETE performance between physical (snapshot based) and logical (basebackup) clones

We have a relatively large PostgreSQL database (500G - 1TB) and want to perform a big migration task. The migration consists in a bunch of ALTER, UPDATE, DELETE, SELECT. We have performed this ...
0
votes
1answer
29 views

pg_trgm index not used with commutator

PostgreSQL 9.6, on a table with ~100000 rows of text up to 3200 characters long. I have the trigram indexes: CREATE INDEX trgm_idx_gin ON test_trgm USING gin (t gin_trgm_ops); CREATE INDEX ...
0
votes
1answer
22 views

Is it possible to do sums of aggregation that stops aggregating once the condition is reached?

Say I have a table amounts with millions of rows tallying up to some number, but I'm really just checking if it's > 100. Is there a way to stop aggregation since taking this: SELECT SUM(amount) FROM ...
0
votes
0answers
28 views

User-defined harmonic mean function performs worse than query in PostgreSQL 9.6

We created a harmonic mean function based on this blog post: CREATE OR REPLACE FUNCTION public.harmean_accum(numeric[], numeric) RETURNS numeric[] LANGUAGE sql STABLE COST 1 AS $h$ SELECT array[$1[1]+...
0
votes
1answer
30 views

Optimization for table with text and time columns

I have a simple table with the following columns: id (int4) time (timestamp) name (varchar) service_name (varchar) For every service (there are not more than 500 different names), there are a lot of ...
0
votes
0answers
66 views

Increase shared_buffers without restarting PostgreSQL

According to the official PostgreSQL documentation: shared_buffers (integer) Sets the amount of memory the database server uses for shared memory buffers. The default is typically 128 ...
1
vote
1answer
42 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 ...
2
votes
0answers
32 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
29 views

pg_restore “CREATE CAST” not executed if using public or pg_catalog schemas

While moving database to another instance I'm using pg_dump and pg_restore with intermediate file. Dump file was created like this: pg_dump --host $SRC_DB_HOST --port 5432 "dbname=$SRC_DB user=$...
2
votes
1answer
46 views

Postgres choosing a filter instead of index cond when OR is involved

I have a table that daily gets about 20 million records added to it, and I'm trying to paginate through it to let people get access to all the data in it, but the query time has to be "decent" (in my ...
0
votes
1answer
181 views

wal_level both hot_standby and logical

I have a PostgreSQL setup with one master and three slaves. It's configured as streaming replication. Now, I want to replicate only a few tables from the master server to an analytic server using ...
0
votes
0answers
58 views

Postgresql - Can we create streaming and logical replication replication from same server

I have a postgresql setup with 1 master and 3 slaves. Now I want to setup a replication for only one database to reporting server. My problem statements: I can't use bucardo (no approval for ...
0
votes
3answers
191 views

Postgres speed up index creation for large table

I have a large Postgres table with 2+ billion entries (1.5TB) and mostly non-null, char var columns. To speed up inserts, I dropped the indexes before bulk uploading. However, it is now taking forever ...
0
votes
1answer
24 views

PostgreSQL 9.6: Update on a single row table takes an unreasonable amount of time

I've got a table called source which has a primary key on its id and a “Unique Constraint” on the column name. This is the way the table looks: id int4 name ...
0
votes
1answer
110 views

pg_stat_statements empty with administrator user and unusable for other users

I have an issue with pg_stat_statements, the extension is installed and enabled on the database, was working for a long time, however it now seems that it has crashed. portaldb=> SELECT * FROM ...
1
vote
2answers
32 views

PostgreSQL partial index not used depending on boolean comparison

I need help understanding why a partial index would be used in one query and would not in second one which is effectively the same. I have the following table: Column | Type ...
0
votes
1answer
55 views

Copy large object field into remote PostgreSQL DB [closed]

We have configured postgres_fdw to move data from a DB to remote DB. It works fine but the large object field's data in source db not getting copied to remote DB. This is the statement used to copy: ...
0
votes
1answer
33 views

PostgreSQL Trigger to override insert on table with inheritance

We have a table documents that is partitioned by time into child tables like documents_2019_01, documents_2019_02. We do this using a BEFORE trigger on the parent documents table to insert into the ...
0
votes
0answers
23 views

How to get record by date from 1 table and update other table in Postgresql?

I have two tables. In one table (order_product) I have multiple records by date and in the other table (Transfer_product) also multiple record by date. order_product table have correct record. I want ...
0
votes
0answers
68 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 ...
0
votes
1answer
23 views

update value in row based on value in another row

I have a table with timestamped data. t_stamp | col1 | col2 | col3 I want to update the value in say col3 based on values in col1 and col2 from the previous row in the table. By previous row I mean ...
0
votes
1answer
73 views

pgAdmin4 version 3.1 how to update to the last version

We use the pgAdmin4 version 3.1 on Red Hat connecting to a server PostgresQL 9.6. In the log of the server, I notice some errors generated by the pga4dash : ERROR: column "waiting" does not exist ...