A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

Questions tagged [postgresql-10]

PostgreSQL version 10

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

how to make postgresql in cluster(HA Active-Active) and auto sync the data between them

For one of our application we need to build the database as mentioned in the title. We have one of our application which need to run in postgresql ha active-active cluster . Were postgresql cluster ...
1
vote
1answer
28 views

54001 stack depth limit exceeded in recursive stable function [closed]

I have a function which calls itself to resolve a hierarchy. Without any volatility declaration, it gives me the desired result (that means the recursion ends). As a performance optimization, I ...
0
votes
0answers
13 views

How to build simple jsonb object out of multiple arrays in postgres?

If I have three (or more) Postgres arrays with the same number of elements, and I want to build a multi-level JSONB object out of them—how can I do this? Example: create temp table tbl1 as select ...
5
votes
3answers
40 views

Allow for dynamic result sets from plpgsql function by filtering based on an array passed in?

I think I've avoided the XY problem here, as I'm laying out my solutions to the real underlying problem (summarizing multiple tables in a dynamic fashion) and I'm only asking about the one final part ...
1
vote
2answers
21 views

Add data directory on PostgreSQL 10

I have a postgresql database on / root partition (61GB used out of 63GB), so I asked the VPS provider to add more disk (50GB to /mnt/vdb1) How to add another data directory to that new partition (...
0
votes
1answer
26 views

Blocking drop table for specific schema using trigger

I'm trying to prevent anyone from dropping tables on a specific schema "public" unless the user is an rds_superuser but the function I wrote is guarding on all schemas. CREATE OR REPLACE FUNCTION ...
0
votes
0answers
7 views

AWS Aurora RDS 2nd reader gets no queries

Running a Postgres Aurora RDS with one writer and two readers. One reader is used heavily, but the other is barely used at all according to CloudWatch. The Laravel application this is connected to ...
0
votes
0answers
13 views

Trigger on update of any columns to copy the update to another able

I am partitioning an existing table in production called app_users, to do this, I have created a partition table app_users_partition with it's partitions along with a trigger in app_users which copies ...
2
votes
0answers
54 views

pg_stats is huge and slowing PostgreSQL server down… What do I do?

I have two servers running PostgreSQL 10.11 that are configured the same, run on comparable hardware, contain the same databases and (mostly) the same tables. Various Nagios checks using ...
-1
votes
1answer
62 views

Problem with indexing null values

There is a table 'test' with ~ 10M records with field 'finish'. select count(*) from test where finish is null; count --------- 2485009 (1 row) I'd like to create index to speed up queries like ...
0
votes
2answers
28 views

Filter array attribute in jsonb column type using jsonb_array_elements_text

My Postgres version: 10.9 Using JsonB Column type which has a below structure: columnName: favorites { "favouriteCity": "Boston", "favoriteColors": ["Blue", "Red", "Green"], } Now, I want to filter ...
1
vote
1answer
26 views

json_agg with dynamic 0-level key

I'm migrating a schema from NoSQL to postgres. Today's object "b" exists in NoSQL as an element of object a & looks in source like this... { "a": { "id": 1, "b":{ "c1": { "...
0
votes
1answer
57 views

Postgres Aurora RDS password auth fails except with master user

I can successfully connect to a Postgres Aurora server using the master password thru the SSH tunnel on the bastion host I have setup. However, when I create any other users, I can't connect with ...
0
votes
2answers
27 views

How to use pg_restore in database with different postgis installation (postgis schema location)?

I have database1 where postgis is installed in the public schema. And database2 where postgis is located in a schema called postgis. When I dump database1.schema1, schema1.table1 references its geom ...
1
vote
1answer
46 views

PostgreSQL Copy command internals

I need to know some internals about the copy command in PostgreSQL 10. In particular I need to know if the copy from into a remote database makes batch insertions. If so, what is the default ...
1
vote
0answers
24 views

Constraint creation on a large table is too slow

There's a table with 723M rows, large amount of data and not partitioned. We're adding a constraint to the table as follows: ALTER TABLE ONLY my_table ADD CONSTRAINT partial_constraint EXCLUDE USING ...
0
votes
2answers
129 views

Postgres extremely slow after upgrade from Version 10 to 11

Related to this:PostgreSQL extremely slow after upgrade from 9.3 to 9.4 but trying to understand better... My queries was slow after upgrading to Postgres Version 11.5 from 10.7 I first ran ANALYZE ...
0
votes
0answers
36 views

Postgresql 10: Ldap authentication failing

I configured the pg_hba.conf of Postgresql 10 with following host Tom Tom XXX.XX.XXX.XX/32 ldap ldapserver=XX.XX.XXX.XX ldap ldapbasedn="ou=People,dc=example,dc=com" ldapbinddn="cn=Tom,ou=People,...
0
votes
1answer
8 views

COPY command only works as command line argument, not as part of script

No rows are being copied when I try to copy a CSV from stdin. To be more specific, this works when writing the command on the cli, but not when read as part of a file. Why is reading SQL commands from ...
1
vote
2answers
240 views

Update all values for given key nested in JSON array of objects

I have a jsonb column data in a Postgres table my_table. It contains the following data: [ {"id":"1","status":"test status1","updatedAt":"1571145003"}, {"id":"2","status":"test status2","...
0
votes
2answers
38 views

PostgreSQL 10 Clean Up After Restore

I run postgres server (10.6) on my pc just for some custom programs. Problem is every time I restore a backup file of 600mb - the pg_wal directory increases in size for 600mb. Is there a way to easily ...
-1
votes
1answer
394 views

repmgr on PostgreSQL 10 failed, recovering

I'm running a 3 nodes PostgreSQL 10 + repmgr 4.4 cluster: 1 master node (postgres1.domain.local) 2 stabdby nodes (postgres2.domain.local and postgres2.domain.local) The cluster was running fine ...
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) ...
0
votes
0answers
80 views

How to start, stop, pause and resume a logical replication setup in PostgreSQL using PgLogical?

I have a publisher node on on-prem 9.4 and subscriber node on RDS version 10.6 and few tables are configured for replication. The current status is replicating. Now for my learning, I want to stop the ...
-1
votes
1answer
67 views

Simple “SELECT” query taking too long

I'm trying to run a SELECT query on our PostgreSQL database using pgAdmin 3. It looks like this: SELECT tstamp AS "time" dvalue AS "Weight" FROM tablename WHERE tstamp BETWEEN 'time1' ...
1
vote
1answer
42 views

How to make Postgres Multi-Column and Multi-Table Search More Efficient

I have a Shipment table with some basic data about a shipment and a ShipmentItem table which contains additional attributes about that shipment with a foreignKey on the Shipment table's primaryKey. ...
2
votes
1answer
27 views

Creating a view (or table) from JSON data with two different shapes

I'm pulling in JSON data from two different sources that have roughly the same information, but in different shapes. Let's say I'm pulling in data about music albums, and one of the API responses ...
0
votes
0answers
56 views

FATAL: pg_hba.conf rejects connection for host … SSL OFF

Before this issue happen, it work perfectly fine for months. I am had try the configuration below at DO droplet: 1) Change pg_hba.conf host all all 0.0.0.0/0 trust 2) I issue the restart commond ...
1
vote
1answer
114 views

PostgreSQL read only user

We have a PostgreSQL database 'db1', which is having around 500 schemas. I am trying to create a read-only user for this particular PostgreSQL database. I could find the following command for ...
1
vote
1answer
364 views

uncommitted xmin during vacuum, what to do?

Running vacuum on two specific tables in my postgresql 10 database results in the following error: VACUUM (FULL|ANALYZE|FREEZE|) table; ERROR: uncommitted xmin 359569171 from before xid cutoff ...
1
vote
1answer
32 views

Alter table add column with no default blocks

tl;dr The following query blocks the postgres server when executed ALTER TABLE "image_data" ADD COLUMN "user_status" varchar(30) NULL engine version 10.6 # of rows 1.5 mils Hi, I have a somewhat ...
0
votes
0answers
31 views

Order of operations in LATERAL join?

Consider the following query to find the (spatial) nearest neighbor; the PostGIS specific components here should not be of importance for my question, or so I believe: SELECT a.id, c.geom FROM ...
0
votes
1answer
26 views

Create view with dynamically selected tables - is it possible?

I have subsection of tables in my database that share part of a schema and a naming scheme. I'd like to create a view that automatically gathers all of the common columns of these tables into a single ...
0
votes
0answers
39 views

PostgreSQL 10 not allowing remote connection

I cant get a PostgreSQL 10 instance to allow remote connections. The database is hosted on Ubuntu 18.04 listen_addresses has been set to both '*' and 'myactualip' that needs to connect to the ...
0
votes
0answers
172 views

PostgreSQL - Update not working inside function

I am creating a web app that handles coupons. When a user claims a coupon the coupon batch count "should" be updated, and a new coupon is created. For some reason the UPDATE query does not work ...
0
votes
1answer
78 views

Why pg_wal dir does not clean up while logical replication

my main question - why pg_wal dir is extremely growing up? I have two postgresql instances. The first was set up as master: wal_level: "logical" max_wal_senders: 5 max_replication_slots: 10 Also, ...
0
votes
0answers
10 views

How to find the master instance of a read replica in AWS RDS PostgreSQL?

I have an RDS Postgres instance whose role is Replica. I do not see the master in the same region and probably is in another region of the AWS account. How do I find the master for this replica? ...
2
votes
1answer
112 views

Postgres wait_event_type Extension

I have a function that was written in Language SQL (not plpgsql) that is scheduled to be called nightly from pgAgent. It was running fine until last night. The function usually takes under 3 minutes ...
0
votes
1answer
83 views

How to extract the grants on default roles of PostgreSQL 10: pg_read_all_settings, pg_read_all_stats, pg_monitor and pg_signal_backend?

I have a Postgres instance of version 10 running on AWS RDS. I have a user there with default roles granted to it. I want to create the same user on my sandbox where the instance is of version 9.4. ...
1
vote
0answers
110 views

Zabbix on Postgresql - very slow deletion of event

Were using Zabbix for monitoring and I'm having problems understanding why deleting events is so slow. Zabbix: 4.2 (never mind the name of the db - it is 4.2) Postgresql: 10.8 nvps: ~400 hosts: ~600 ...
5
votes
1answer
223 views

pg_ctl hangs over ssh

I am designing a process to test my postgresql 10.8 backups by restoring them to a random point in time in a throwaway virtual machine. I have been unable to fully automate the process though. I am ...
1
vote
1answer
419 views

Query performance of index scans slower than parallel seq scan on Postgres

I've been trying to debug a performance problem on PostgreSQL 10 on AWS Aurora. Specifically I'm trying to understand performance problems on "cold" queries where the buffer cache does not contain ...
0
votes
0answers
15 views

Recipe for target be-secure-openssl.o failed in Postgresql

I'm installing postgresql-10.2 server. while doing installation, I'm facing problem when I execute make. The below line I've used to configure, ./configure --prefix=/data/postgresql --with-pgport=...
0
votes
1answer
92 views

Postgres parallel seq scan making high CPU Utilization

When I run a select query which runs on a view in Postgres, it is starting 3 processes for a single query, which is causing high CPU utilization when multiple job runs in parallel. How to avoid using ...
4
votes
0answers
59 views

PostgreSQL 10 backend processes

I have a particular issue with PostgreSQL 10: Postgres creates backend processes for each connection on different ports. The problem is that some of our apps also use many ports and sometimes they ...
1
vote
2answers
50 views

Multiple aggregates with cross (lateral) join

Following on from Combining array_agg and unnest, given this data: key | a | b | c ------------------ 1 | 0 | 1 | {1,2} 1 | 1 | 2 | {3} 1 | -1 | 3 | {2} 1 | 2 | 4 | {} Executing this ...
1
vote
2answers
411 views

Locale is incompatible error in Postgresql 10 when replicating from Ubunu to Win 10

The locale available in my Ubuntu PG-10 DB is en_US.UTF-8. When doing PG_Basebackup to a PG-10 DB in Win-10, Psql failed to connect with error psql: FATAL: database locale is incompatible with ...
2
votes
1answer
61 views

PostgreSQL. Unstable execution time hugely exceeding planning time

PG version: 10.9 Schema: create table core_bundle ( id serial not null constraint core_bundle_pkey primary key, dt_add timestamp with time zone not null, dt_upd timestamp with time zone ...
1
vote
1answer
54 views

Duplicate rows with primary key constraint in PostgreSQL 10.6

I have a table that's had CONSTRAINT table_pkey PRIMARY KEY (id) since setup. However, I recently found that I have around 100 duplicate rows (I ran select count(*) from (select count(*) from table ...
2
votes
1answer
784 views

Combining array_agg and unnest

Given a dataset (with a GIN index on values): key | values ------------- 1 | {4,2,1} 1 | {2,5} 2 | {4,1,3} I want to aggregate the arrays: key | values ------------- 1 | {4,2,1,5} 2 | {4,...