Questions tagged [postgresql-10]

PostgreSQL version 10

Filter by
Sorted by
Tagged with
-1
votes
1answer
52 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
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
2answers
93 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, ...
0
votes
0answers
18 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
1answer
221 views

Does CREATE/REFRESH MATERIALIZED VIEW in PostgreSQL use the parallel planner?

I am wondering whether CREATE (with data) or REFRESH MATERIALIZED VIEW uses the parallel planner? When I EXPLAIN ANALYZE the query by itself (any query with parallel potential works here) without ...
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
23 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
10 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 ...
5
votes
1answer
3k views

What is the data type of the ‘ctid’ system column in Postgres?

The Postgres system columns are documented in Chapter 5. Data Definition > 5.4. System Columns. That page mentions that oid values “are 32-bit quantities”. And that page says the same about ...
1
vote
1answer
38 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
44 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 ...
0
votes
1answer
347 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 ...
1
vote
1answer
24 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 ...
1
vote
1answer
122 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
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
24 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
3answers
3k views

How to restore a pg_dumpall dump without CREATE INDEX?

I am attempting to migrate from Postgres 9.6 to 10.3 and during the restore each index is recreated one by one - this is a problem. So far I thought pg_dumpall is a good option. pg_dumpall -U ...
1
vote
1answer
136 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 ...
0
votes
1answer
20 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, ...
6
votes
1answer
2k views

Multiple on conflict targets

I have two unique indexes on columns a and b. I need something like this: insert into my_table (a, b) values (1, 2), (1, 2) on conflict (a) do update set c = 'a_violation' on conflict (b) do update ...
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 ...
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
36 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
2answers
103 views

Postgres file increment size

I'm COPY-ing 300 tables / 400G to PG10@win2016server using a few threads to speed things up, but because the table files are incremented in 64-kb units (or something there-about) they get fragmented ...
0
votes
1answer
130 views

Query in PostgreSQL became slow and then got better on its own - why?

I want to better understand how to debug and fix performance issues in PostgreSQL. I have a query that usually costs 1 second to run. However, for some reason, it started taking 30 seconds to run for ...
1
vote
2answers
155 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 ...
0
votes
1answer
29 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
30 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
185 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 ...
2
votes
1answer
229 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 ...
1
vote
2answers
361 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....
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
33 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
52 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
3answers
195 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
32 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
73 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 ...
1
vote
1answer
40 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
53 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 ...
2
votes
1answer
189 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
90 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 ...
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 ...
13
votes
1answer
522 views

Why is PostgreSQL choosing the more expensive join order?

PostgreSQL using defaults, plus default_statistics_target=1000 random_page_cost=1.5 Version PostgreSQL 10.4 on x86_64-pc-linux-musl, compiled by gcc (Alpine 6.4.0) 6.4.0, 64-bit I've vacuumed and ...
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
40 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
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 | +- ...
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
1answer
200 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 ...