Questions tagged [postgresql-10]

PostgreSQL version 10

Filter by
Sorted by
Tagged with
0
votes
1answer
6 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
28 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
30 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 ...
0
votes
1answer
44 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
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) ...
-1
votes
0answers
14 views

PostgreSQL index scan is slow [duplicate]

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' ...
0
votes
0answers
31 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
62 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
34 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
25 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
19 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
51 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 ...
2
votes
1answer
96 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
25 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
19 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
25 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
33 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
31 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
26 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
8 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
0answers
42 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
39 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
38 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
193 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
161 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
12 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
43 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
53 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
36 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
1answer
119 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
54 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
43 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
282 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,...
0
votes
1answer
18 views

Can I use SELECT statement for values in a list partition? Or do I need to explicitly state them?

So I have a table I would like to partition, and in creating the partitions I am trying to run a statement like this: partition by list(apple_type) create table foo.apples_green partition of foo....
0
votes
1answer
45 views

PostgreSQL >= 10 does not generate WAL if there are no transactions

I noticed that PostgreSQL >= 10 does not generate WAL if there are no transactions. In previous versions, PostgreSQL generated WAL at the expiration of archive_timout, because it wrote some empty ...
0
votes
1answer
133 views

json_object_agg errors on null in field name

PostgreSQL versions: my local install 11.3 and the below fiddle is on 10.0. Both behave the same. I have a schema of pages, each page has sections and each section can have different kind of ...
0
votes
1answer
41 views

Moving a hard disk with PostgreSQL data folder to another server

I'm running a NAS server with an SD card mounted as / and RAID-1 storage mounted under /media/hdd. /etc folder is on SD card. PostgreSQL data folder is on the RAID-1 disk. /-+ +- etc | +- ...
0
votes
0answers
15 views

Postgresql to log errors caught within BEGIN/EXCEPTION/END blocks

I'd like to have errors caught within a BEGIN/EXCEPTION/END block logged. Here's what I have, compare 1. and 2. -- 1. Nothing will be logged CREATE OR REPLACE FUNCTION func() RETURNS VOID AS $$ ...
1
vote
1answer
267 views

Postgres 10 Query waits on IO: BufFileWrite causing failure to get new database connections

I have a query (attached with query plan) that is run every 5-7 seconds from every device that is running our dashboard (typically, 500+ devices at peak time). This query, at the outset looks like it ...
3
votes
1answer
90 views

Slow performance on partitioned PostgreSQL table when using now() to filter

I have a PostgreSQL v10 table partitioned on a timestamp field: CREATE TABLE front.probes ( bucket_start timestamp NOT NULL DEFAULT now(), -- Extra fields here ... ) PARTITION BY RANGE (...
1
vote
0answers
29 views

pg_dump does not return schema for repmgr objects

While trying to browse repmgr.monitoring_history, I noticed it has many tens of millions of records. Wanting to get a peak at the indexes that (may or may not) already exist on the table, I try a ...
0
votes
1answer
50 views

PostgreSQL 10 Optimizing slow query performance with aggregate function

I have two tables person and tag. person has an bigint[] column containing ids of tags. I want to write a query which fetches all tags including the count of the tagged persons. So far I have the ...
1
vote
1answer
61 views

What are some consequences of putting quotes in a WHERE clause for an integer column?

I have a table with a column some_column of data type integer. I've noticed that my PostgreSQL 10.6 instance is smart enough to interpret both of the queries below as same: select * from my_table ...
0
votes
0answers
51 views

PostgreSQL GIN pg_trgm default operator class - Does Not accept data Type uuid

I was trying to create GIN Indexes for an Array that stores uuid data type. CREATE INDEX IX_Gin_Column1 ON table_name USING gin (column1 gin_trgm_ops); PostgreSQL raised an error: ERROR: ...
1
vote
0answers
38 views

Rollup by unique combinations

I am attempting to do a ROLLUP or CUBE function, and I am wanting to get back unique combinations based on the values of a field, rather than particular columns. Here's what I have so far. With the ...
0
votes
1answer
37 views

Working memory on the connection string for postgres instance

I'm trying to understand how can I set the work_mem setting on the Postgres(v10) for a single connection/session. Currently I'm connection to my Postgres Instance in the cloud through a NodeJs app ...
0
votes
0answers
20 views

After upgrading from PG 9.3 to 10 indices became corrupted

So recently I was upgrading the database in production from Postgres 9.3 to 10. In addition to upgrade, we wanted to move the database to another machine. I know, doing it "in place" would be easier ...
0
votes
2answers
101 views

PostgreSQL 10 Optimizing slow query performance

I have the following query: select i.id as id, i.ts_updated as updated, i."name" as name, cast(attr.data_norm as text) as data_norm, cast(_59.json_agg as text) as _59, ...
3
votes
0answers
215 views

Restoring from PostgreSQL 9.5 dump to 10 is super slow

I am trying to restore database dump created on PostgreSQL 9.5.16 to PostgreSQL 10.7 and it is taking a lot of time to restore. Restoring the same dump to 9.5 doesn't take that much. So I am wondering ...
0
votes
0answers
25 views

Search path for DMS postgres 10 wrapper functions

I'm setting up DMS to perform replication from a non-RDS database. After running the suggested code to create wrapper functions, I'm finding our migrations are breaking with errors that indicate ...