Questions tagged [postgresql-10]

PostgreSQL version 10

89 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5
votes
0answers
435 views

Postgres 10 Logical Replication without truncating existing table

I have been fooling around with Postgres 10 logical replication, trying to learn about it and potentially apply it to a couple of projects. I have observed something that I am wondering about, but I ...
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 ...
4
votes
0answers
122 views

PostgreSQL autovacuum ignoring “inactive” tables resulting in “transaction id wraparound”

I have a PostgreSQL database with a lot of tables (currently 93k) and I'm seeing issues with transaction id age threatening wraparound, because autovacuum is not getting triggered for clean tables ...
3
votes
0answers
214 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 ...
2
votes
1answer
91 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 ...
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 ...
2
votes
0answers
38 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 ...
2
votes
1answer
103 views

SELECT operation with four joins results in bad performance

I'm having problems with a slow query. The query purpose is to get doers ids for X job who didn't exceed specified limits on realizations and also are in range of possible job places. Resources ...
2
votes
0answers
48 views

Automating performance tests in PostgreSQL

I want to check the performance of specific queries in a Postgres database on a regular basis. Also, I want to be able to check the change in performance of different queries with any change in my ...
2
votes
1answer
426 views

Any workarounds for lack of primary key on partitioned tables?

I am currently looking at implementing declarative partitioning with PostgreSQL 10. However, one sticking point is that we need to have a foreign key between two such partitioned tables, which is not ...
2
votes
0answers
1k views

Downgrade working postgres cluster 10 to 9.x

Is there anyway to downgrade working postgres cluster 10 to 9.6? There are major changes (i.e, pg_wal / pg_xlog, etc.,) been done in 10. So far, I decided to take dump and restore into new 9.x cluster....
2
votes
1answer
238 views

Have several 128 elements lists, need to obtain distance between them

Is storing them in a CUBE type field and then getting euclidean distance, (using <->) the only way? I can not save them as a list of floats, since i get: "can't adapt type numpyndarray" Can ...
2
votes
0answers
345 views

ts_headline() not highlighting properly for <-> operator

ts_headline() does not seem to highlight the input text in a way that respects the <-> operator. The following query: SELECT ts_headline('English','This Commercial Bank does not have any ...
2
votes
0answers
231 views

Intarray is available but can't seem to be able to use the icount function

My question is below. Following the suggestion from one of the comments, I started to search for search_path and extensions. I came on to this post: https://stackoverflow.com/questions/12986368/...
2
votes
0answers
248 views

FDW-based Sharding with PostgreSQL 10: Multiple coordinator nodes?

Context I'm currently looking for and comparing different options for sharding data from a Postgres database into multiple ones. The end result may be something like having instances of a web ...
1
vote
0answers
37 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 ...
1
vote
1answer
157 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 ...
1
vote
1answer
42 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
0answers
28 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 ...
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 ...
1
vote
0answers
51 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
0answers
161 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 ...
1
vote
0answers
50 views

Postgres 10.6: UPDATE on partially indexed column sometimes hangs

I have a table (6 small columns 2M+ rows) with a column dead. There are two indexes on this table, both are partial with dead = false. From time to time I want to scan the table and mark rows with ...
1
vote
0answers
17 views

Best practise for data dump 'entry point' in DB processing chain - PostgreSQL

I need to implement a process chain to transform and analyze potentially large sets of data, inserted both programmatically and manually (e.g. CSVs). In either way, the input data is standardized in ...
1
vote
0answers
102 views

Logical replication sync time

I'm trying to create a logical replication between 2 Postgres 10 databases in separate clusters. All the process seem to be working, no error and \dRs+ shows the subscription correctly. The master ...
1
vote
1answer
2k views

Downgrading PostgreSQL 10 to 9.4

Are there any particular precautions I should take when downgrading PG 10 to 9.4? I have a database on Heroku (PG 9.4) which I'm going to upgrade to PG 10.6. In case something goes wrong, I want to ...
1
vote
0answers
130 views

Make use of PostgreSQL 10 multi-column statistics when planning index scans

I'm using PostgreSQL 10 and am trying to optimize query performance on a large table (~70M rows). The query looks like: SELECT * FROM large_table -- many joins -- WHERE col_3 = 'particular date'::...
1
vote
1answer
2k views

could not load library “/usr/pgsql-10/lib/postgis-2.5.so”: /usr/pgsql-10/lib/postgis-2.5.so: undefined symbol: GEOSFrechetDistanceDensify

I have installed postgresql 10 on centos 7.4 and installed postgis2.5 few months back. The installation was successful and Iam able to use postgis. All of a sudden Iam getting the following error. ...
1
vote
0answers
490 views

PostgreSQL 10.3 pg_dumpall fails: server closed the connection unexpectedly

System: Windows 10 Pro 64bit, Version 10.0.17134 Build 17134. with Postgres 10.3 db server. I backup my postgres db using the following statements: "C:\PostgreSQL\pg10\bin\pg_dumpall.exe" -U ...
1
vote
0answers
18 views

Referencing a value of a prior column in a default expression in xmltable()

I'm trying to reference a preceding column c1 in a default value of another column c2 in the xmltable() function, but I get an error: column c1 does not exist. SELECT * FROM xmltable( '.' PASSING ...
1
vote
0answers
22 views

SET TIME ZONE '-7' vs. AT TIME ZONE '-7'

This is similar to these questions: Why does PostgreSQL interpret numeric UTC offset as POSIX and not ISO-8601? Strange UTC offset time zone parsing in Postgres I'm running Postgres 10.3. As per the ...
1
vote
0answers
149 views

postgresql 10 - standby flips to read/write

I am trying to setup a primary/standby for failover; however, the standby flips to read/write and no longer functions as a replication database after the copy from the primary. Additionally, the ...
1
vote
0answers
12 views

Show each role permission for each database

Show each role permission for each database. I found a query in an answer, but it shows only for connect(in the same row with commas) I want to show for every privilege. If I add in the were ...
1
vote
0answers
184 views

PGpool not properly detecting which postgres server is master

I am in the process of building a resilient database cluster and currently have 3 database servers running postgres 10 and repmgr. repmgr is taking care of the streaming replication including ...
1
vote
0answers
435 views

How to connect to Postgres on localhost with URL: “jdbc:postgresql://database:5432/db123”

I am able to connect to a postgres 10 instance on my local Win10 machine with pgAdmin 4 and "localhost" as a hostname, but when I change the hostname to "database" I cannot connect. How to fix it? I'...
1
vote
0answers
30 views

PostgreSQL- user-defined types: note sure what internallength/alignment is for linked list type

I'm currently trying to implement at user-defined data type in Postgres in the form of a linked list with each node holding an int and a pointer to the next node, here are my input functions: // ...
1
vote
0answers
182 views

why order by desc and limit query is taking too much time?

Currently, i was trying to make a sql query on my postgresql table, all things was going good unless i sort my query by Price in DESC, after this filter query is taking almost round about 60 sec to ...
1
vote
0answers
192 views

Postgres pg_dump restore performance. Is using pg_dump to back up large databases an acceptable solution

Folks, I am considering moving from SQL Server to Postgres. The per core licensing of SQL Server is too expensive for my needs. My one concern is restoring Postgres from a dump. I believe that the ...
1
vote
0answers
64 views

Partitioning in PostgreSQL 10

Currently we are using pg_partman for partitioning our tables in PostgreSQL 9.4. We are planning on upgrading to PostgreSQL 10. I read that PostgreSQL 10 has better support for partitioning. While I ...
1
vote
2answers
158 views

Postgres 10.3 : table grows in size after delete-insert

I'm a rather new to Postgres and the issue I see seems a bit strange. Maybe you as experts know something about it. I have a table (around 3 million rows and 200 columns). In order to populate fresh ...
1
vote
0answers
95 views

Issue with GIST indexing an ltree column

I recently read about the ltree column & how it could, supposedly, be GIST indexed to provide relatively good support; this seemed like a good match for a problem I'm currently playing with, so I ...
1
vote
3answers
209 views

How to get YAML Python Library in PostgreSQL

I would like to use YAML in some plpython code, but YAML is not included in the python3 extension for PostgreSQL. My 'import yaml' gets an error that it cannot find yaml. On my regular Python3 ...
1
vote
2answers
375 views

Empty table after pg_restore

I have a backup of an unlogged table called potential_users, the relation has 58677237 records in it. Steps: pg_dump --format custom --verbose --file "potential_users.backup" --table "public....
1
vote
1answer
142 views

Installing Postgresql parray_gin extension on mac, include not found

I am trying to install parray_gin in PostgreSQL 10 on macOS, but I can't get it to build. I'm running pgxn install parray_gin. This first gave me a missing stdio.h error, so I installed the xcode ...
1
vote
0answers
86 views

How do read queries against Postgres's JSONB type compare to Couchbase?

I'm working on a small project which would involve running queries against deeply-nested (up to 5 layers) keys in dictionaries. This would be specifically a read-heavy environment, with only one large ...
0
votes
2answers
29 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
41 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
0answers
30 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 ...
0
votes
0answers
16 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 ...
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 ...