Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

Filter by
Sorted by
Tagged with
0
votes
0answers
12 views

What it is better to choose for installation of postgresql - debian or ubuntu? On base big loading [closed]

I have a big Postgres database on Windows the server. 200 GB, are a lot of different requests. I plan migration this base to Ubuntu. What distribution you recommend to use? Ununtu 18.04 or Debian 9/...
0
votes
0answers
33 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
39 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
28 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
16 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=$...
1
vote
1answer
36 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
36 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
23 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
64 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
23 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
43 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
29 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
34 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
20 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
43 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
22 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
42 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 ...
2
votes
1answer
55 views

Slow performance on postgres query with count/sort

I've inherited an Django/postgres app from another developer and I'm finding many of the queries are very slow. I've spent a lot of time correctly organising/normalising the data which was not done ...
0
votes
0answers
22 views

UPDATE query in small tables taking forever

I'm having trouble creating a UPDATE query to perform the following: I have two tables, ed is a 11k rows table with areas from multiple constructions and a key lote_key wich links to the table unid, ...
0
votes
1answer
29 views

Progressive child tables scan in PostgreSQL?

We have append-only events table where events from different devices are being collected. We use PostgreSQL 9.6 and pg_partman to split the table into monthly partitions. Data is partitioned using ...
0
votes
1answer
30 views

Killing long running queries with user notification

Is there any way to inform user that your query has been running more than x minutes, so admin has killed your session. This is especially to the query or read only user. I have a function which will ...
0
votes
1answer
27 views

Is there a place to store ERDs in Postgres?

Is there a designated spot in Postgres to store Entity-Relationship Diagrams (ERDs)? I could just create a table with a BTYEA column or something, but I want to make sure to use what exists if ...
3
votes
2answers
86 views

Database design that requires an index over two tables

Using PostgreSQL (currently 9.6, but upgrades are possible), I currently have the following database layout where customers can order products, which are themselves sorted into categories (products ...
0
votes
1answer
31 views

How to select blocks from a result by condition?

Example table: master_id 1 created_at 22.02.1997 master_id 1 created_at 22.03.1997 master_id 1 created_at 22.04.1997 master_id 1 created_at 22.07.1997 master_id 1 created_at 22.08.1997 master_id 1 ...
0
votes
0answers
12 views

Can I use EBS data directory path as temp tablespace in ADS Aurora Postgresql?

I am having issue with RDS Aurora Postgresql's root storage (which is used for Temp files/tables and Logs) since its basically 2*PhysicalMemory. I thought of creating a new tablespace in data storage ...
0
votes
0answers
57 views

work_mem setting for huge queries

I am querying some data from a view using copy (select * from tbl where id in (n) order by 1) command (the table's in the view are ~600GB, almost all the columns included in the view) and it generates ...
0
votes
0answers
47 views

RDS Aurora postgres temp files removal

A report application generates lots of temp files in RDS aurora postgresql. Temp files loggings shows average 20G files being generated for each query execution. But the query execution not taking ...
0
votes
1answer
18 views

How to add a constraint to a table using a partial index?

I have a simple table: create table item ( lan text not null, disabled date ); My aim is to ensure uniqueness for the lan column over all rows, where disabled is null. I created a partial ...
0
votes
0answers
11 views

How to optimise PostgreSQL planning time

I have a small query using postgresql 9.6.11 and postgis 2.3.1+dfsg-2 select ST_Transform(ST_SetSRID(ST_Point(671699.9999988,6585300.5001232),32633),3006); that is slower than I like for where it is ...
0
votes
0answers
25 views

Do UPDATE statement performance affect indexed column(s) in Postgres?

I have a table which is having millions of rows. While doing update I'm using primary key column in WHERE clause and it uses index scan as per the EXPLAIN plan (within BEGINand ROLLBACK block). I have ...
0
votes
0answers
36 views

PostgreSQL 9.6 slow UPDATE on production server compared to local machine

I have a production vm with centos 7.6 under hyper-v and a windows 10 laptop as a dev machine and both are running postgresql 9.6, on my laptop 10k UPDATE queries only take 9 seconds while on the ...
0
votes
2answers
42 views

Does adding a postgres index concurrently take up disk space gradually or all at once?

I'm adding a unique index (concurrently, over the course of hours or maybe even days, on a column of 5 billion bigints, all of them NULL) on a Postgres 9.6 server that's pretty low on disk space. I ...
0
votes
1answer
67 views

How currval of sequence being preserved during the backup and restore in postgres

If I do backup and restore a huge db (tons of tables in it), will it run without any issues rather rising exception about duplicate key constraints of sequenced column(s). While taking pg_dump (...
1
vote
0answers
20 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 ...
0
votes
1answer
12 views

Postgres 9.6.6 on Ubuntu 16.04, lots of errors

We're running perfsonar on a group of our servers, and while doing some maintenance work found that we get giant logfiles under /var/log/postgresql table bloat on the database of the perfsonar ...
3
votes
1answer
75 views

Query a database specific configuration parameter

I have a postgres 9.6 server with > 100 databases on it. When doing a mass-migrate, I use statements like this: ALTER DATABASE mydatabase SET default_transaction_read_only = false; Which works great....
0
votes
0answers
25 views

I need some help about indexing a JSONB column

I'm a newbie with PostgreSQL and databases in general. I have PostgreSQL version 9.6.8 and in it there's a schema with JSON data in it. Some of the fields are being indexed already, but there's a ...
0
votes
1answer
155 views

In Postgres 9.6, why is the GIN index not used for a JSONB column with text/int array?

In Postgres 9.6, why is the GIN index not used on my SELECT query below for a JSONB column with text/int array? How do I force Postgres to use the GIN index? SET enable_seqscan to FALSE; EXPLAIN ...
0
votes
1answer
85 views

Speed up UPDATE on large table

We are migrating our ids from INTEGER to BIGINT in some of our tables. The first step for us is to create a new temporary column id2 that has the correct type, and then we want to migrate the existing ...
0
votes
1answer
21 views

Putting a constraints on a already made table in PostgreSQL

I was wondering is it possible to add Not Null to a variable created already in the PostgreSQL database. I created a table already, but I forgot to add not null constraints to the variable ?
0
votes
0answers
35 views

Does Aurora Postgres automatically kill process if replication lag is too high?

I have aurora postgres (9.6) master and reader. Time to time its getting swapped and message shows server process(PID 111111) was terminated by signal 9: Killed I didn't kill anything, and suspecting ...
0
votes
0answers
48 views

Postgres query speed widely varies for no perceivable reason

I'm running an instance of Postgres 9.6.11 on AWS. I have a few tables that aren't huge (one with a couple million records, one with a hundred thousand). I'm trying to query for the recommendation ...
1
vote
1answer
36 views

PostgreSQL doesn't generate index-only plan when using NOT-IN but does when using IN with multicolumn covering index

I have two tables: A(id, x, cid) and B(cid). I need to fetch some records from A and excluding records with same cid in B. I have btree index on A (cid, id). Query-1 (NOT-IN): select id from a where ...
0
votes
1answer
36 views

Find queries causing a lot of read IO

I'm trying to find the cause of a higher amount of VolumeReadIOPs in our Aurora Postgres instance on AWS. This is what the past 6 weeks look like: I found the cause of the largest period (02/27 to 03/...
0
votes
0answers
113 views

PostgreSQL disconnection session time

I have a simple app which supports just GET & POST using JDBCTemplate, in tomcat configuration (context.xml) is set where it can find the Database like below: <Resource name="jdbc/MYDB" auth="...
1
vote
1answer
472 views

PostgreSQL error: remote query result rowtype does not match the specified FROM clause rowtype, on remote function call

This is my remote function: CREATE OR REPLACE FUNCTION public._test1() RETURNS record LANGUAGE plpgsql AS $function$ DECLARE rec record; BEGIN select 1,2 into rec; return rec; END $function$; ...
1
vote
2answers
47 views

Grant CREATE SCHEMA (only) to users

Imagine I have one shared database set up for my class that each student has the potential to access (access to the database is open and only requires kerberos auth). The public space is where I ...
0
votes
0answers
15 views

How to create an optimized view that fills in sparse observations with modeled data?

I have a table of sensor observations with obs_ts timestamp, sensor_id text, sensor_val int which is partitioned by month (e.g.: observations_201903). There are approximately 150k sensors, providing ...
0
votes
0answers
16 views

Why doesn't tsrange('2018-01-01', NULL) @> transactions.transacted_at use the b-tree index? [duplicate]

Given CREATE TABLE transactions ( transacted_at TIMESTAMP ); CREATE INDEX IF NOT EXISTS index_transactions_on_transacted_at ON transactions (transacted_at); Then this doesn't use the index: ...