Questions tagged [postgresql-10]

PostgreSQL version 10

Filter by
Sorted by
Tagged with
0
votes
1answer
17 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
7 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
29 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
21 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. ...
0
votes
0answers
18 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
174 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
87 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
21 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
48 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
26 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
32 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
51 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
36 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 ...
1
vote
1answer
75 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
15 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
29 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
47 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
35 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
14 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
128 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
41 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 (...
0
votes
0answers
18 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
47 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
39 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: ...
0
votes
0answers
36 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
28 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
17 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
76 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
151 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
15 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 ...
0
votes
0answers
26 views

Is there a way to restore contents of pg_largeobject for BLOB data using pg_restore?

Product: PostgreSQL 10.3 We have a need to take a copy of our production database and restore that into a separate environment for testing and training purposes. The problem is that the database is ...
0
votes
0answers
15 views

Configure DB to respond only from a specific IP address

I have a server with multiple IP addresses (say ... 10.1.1.10 & 10.1.1.11) with several databases. And I want to say DBx is only responding if the client uses the IP 10.1.1.10 and DBy is only ...
1
vote
1answer
94 views

Recommended postgres parameters for index creation with 244gb RAM and 32 CPUs?

I have a 200 GB database with 26 tables that I need to create indexes on. Currently index creation takes a long time on the biggest tables, as they have ~12gb and 400,000,000 rows in them (>4 hours)....
0
votes
0answers
41 views

Designing a table with precalculated team values for fast read queries

My version of PostgreSQL is 10.7. As it's something I can't calculate on the fly due to the amount of times it might be used, I decided to store all the 11 million combinations and their result on a ...
1
vote
1answer
29 views

How can I check available space of an index's pages in PostgreSQL?

For tables, I can simply use pg_freespace to check available spaces like below: postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename'); count | ?column? ...
0
votes
0answers
27 views

Insert performance rapidly slowing down [duplicate]

I have a source table with about 500,000 not normalized data and insert it into 4 different tables related by foreign key constraints. The first couple of thousand rows are inserted at a speed of ...
1
vote
1answer
254 views

PostgreSQL: pg_rewind complains about wal_log_hints = on not been set, it is

I'm rather new to pg so please bear with me. I've set up two hosts and installed pg10 on both. Then I configured log streaming replication between them. This worked fine. I wanted to test fail over,...
2
votes
1answer
81 views

Logical replication - Manually triggering update of data on destination tables (after schema change)

I'm testing out Logical replication on Postgresql (10.4 at the moment) with a quite specific use case. I have some central DB (publisher) from where I need to replicate few tables to distributed DB's ...
0
votes
1answer
328 views

ERROR: could not connect to the publisher: FATAL: no pg_hba.conf entry for replication connection from host

I am trying to create a logical replication between 2 postgres servers. But when I try to create a subscription from the slave to the master I get the error ERROR: could not connect to the ...
0
votes
1answer
105 views

Safe to use the same temporary table name in parallel transactons?

Using postgreSQL 10 or 11, would it be safe creating temporary tables with the same name, each carrying different data? I do not intend to share the data between any sessions/connection/transactions. ...
0
votes
0answers
29 views

Is there a possibility to run 2 minor Postgres version in parallel (Postgres 10.x)?

I have Postgres db instance 10.6 running in production say node-b: rhel 7.6 while I am building another cluster (not instance) of 2 nodes. Out of these 2 nodes one posgres node will reside on this ...
0
votes
1answer
435 views

Postgres 10 and malformatted array literal when using EXECUTE

I am trying to create a function on Postgres 10 that would check whether all the given ids exist in the target table. CREATE OR REPLACE FUNCTION "public"."has_mismatching_ids"( _ids TEXT[], ...
2
votes
0answers
36 views

AWS RDS PostgreSQL: Significant query runtime difference between two (identical?) instances

TL;DR: Solution The problem was that one instance hat magnetic storage attached and was therefore significantly slower. Original Question I have two identical AWS RDS PostgreSQL 10.6 databases ...
0
votes
0answers
22 views

How to optimize a query with select, row number and a lot of LIKE

I try to find search rank of a product in today's product history records. For this I use 6 ILIKE and 2 inner join. I have indexed all columns but still so slow. It takes approximately 10 sec. to ...
1
vote
0answers
117 views

Why is restart_lsn not set, even on checkpoint - postgresql

A logical slot will emit each change just once in normal operation. The current position of each slot is persisted only at checkpoint, so in the case of a crash the slot may return to an earlier ...
0
votes
1answer
1k views

Error when creating a stored procedure in postgreSQL-10 [closed]

I couldn't get my first stored procedure to work, so I copied this example from the manual: CREATE PROCEDURE insert_data(a integer, b integer) LANGUAGE SQL AS $$ INSERT INTO tbl VALUES (a); INSERT ...
5
votes
2answers
129 views

Why are partial PostgreSQL HASH indices not smaller than full indices?

I want to create the most efficient index for a sparsely populated column. I only need equality operations, so a HASH index should be beneficial. Now I'm wondering why a partial HASH index isn't ...
0
votes
1answer
330 views

Preparing query in postgreSQL with variable table name?

I'm trying to do an INSERT INTO a table whose name is in the variable myTable. In MySQL, I'd do it like this: SET @SQL = CONCAT('INSERT INTO ',myschema'.'mytable,' SELECT action_name, user, ...'); ...