Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgresql-10]

PostgreSQL version 10

1
vote
1answer
19 views

What happened to pg_xlog?

There uesd to be a directory called pg_xlog that stored all of the WAL logs in PostgreSQL? Part of restoring a basebackup under the archiving scheme required me to copy the WAL into DATA_DIR/pg_xlog. ...
0
votes
2answers
27 views

Postgres jsonb vs composite type performance differences

What considerations are involved in choosing between a jsonb column and a composite type column of the same structure? For example, consider a column like that used in the Postgres documentation: ...
0
votes
0answers
9 views

Utilizing DBLINK[_EXEC] to control transactions within PL/PgSQL functions?

I am exploring options to control transactions within functions, mainly to reduce the memory footprint for large operations over multiple individual units. I.e. large data-sets are inserted regularly, ...
0
votes
1answer
15 views

archive_command failed causing master to run out of disk space. Is there a command that prioritizes master's health

I had replication set up with a simple archive_command in postgresql.conf: archive_command = 'rsync -a %p _postgresql@serverip:/var/postgresql/wal_archive/%f' Which worked great for 2+ years until ...
1
vote
0answers
13 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 ...
0
votes
2answers
50 views

How to get sum of last previous X days of everyday?

Let's say I have sales number of last 60 days. How would like to get sum of every last X days of everyday. For example my graph will include Sum of Day - 60 to 30 Sum of Day - 59 to 29 Sum of ...
0
votes
0answers
18 views

Query error when trying to SELECT data in PostgreSQL 10 [migrated]

I'm just getting started with PostgreSQL and have a bit of a problem. I am using Psycopg2 and hosting the PostgreSQL database on AWS RDS. Here is the query I am trying to execute: cursor.execute('...
1
vote
1answer
63 views

Adding LEFT JOIN to a query has increased execution time 10 times

On a CentOS 7.6 server (Intel Core i7-6700, 64 GB DDR4 RAM, RAID1 SSD) I run a backend written in PL/pgSQL and Java for a mobile and desktop word game with the following Linux packages: postgresql10-...
1
vote
0answers
24 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 ...
0
votes
0answers
25 views

Filter rows by objects in jsonb array in PostgreSQL

I have a json document for each row in my table (PostgreSQL 10) with the following structure { "partyboat": { "participants": [{ "name": "Bill Lambeer", "role": "Lessee" }, { ...
0
votes
0answers
25 views

Create new table with unique foreign key or Nullable columns for one to zero/one relation? Better design?

I am newer to database design, and was hoping I could get some input on a concept I have not seen that much discussion on when I look it up. I am using Postgres 10. Basically, I have a User table ...
2
votes
1answer
57 views

“ERROR: timestamp out of range” converting epoch stored as bigint to a timestamp

I'm getting ERROR: timestamp out of range: "1.52701e+15" when trying to convert an epoch stored as a bigint into a timestamp (value is taken from a real database table): select to_timestamp(...
5
votes
1answer
44 views

Do BRIN indexes support ENUM types?

BRIN indexes seem useful, but I'm not sure how to use one on an ENUM type. I thought this code would work: CREATE TYPE test_enum AS ENUM ('a', 'b'); CREATE TEMPORARY TABLE my_table ( x test_enum ...
0
votes
0answers
14 views

Postgresql streaming replication slow on macOS

I am using PostgreSQL 10.1 on MAC on which I am trying to set up streaming replication. I configured both master and slave to be on the same machine. I find the streaming replication lag to be slower ...
2
votes
1answer
55 views

PostgreSQL JSONB update array elements query

Given a table in PostgreSQL 10: CREATE TABLE forms (id serial, form jsonb); INSERT INTO forms (id, form) VALUES ( 1, '"id":"a uuid_v1 here", "widgets": [ {"id":"1", "permissions": ["user1", "...
1
vote
1answer
53 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 ...
0
votes
1answer
18 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
0answers
41 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
votes
0answers
23 views

View with Foreign (Tables / Data Wrappers) on PostgreSQL

I have two tables in two different databases which contains records on almost same schema. In order to make the access to viewing that data easier, I created a foreign table in one of the databases ...
-1
votes
0answers
71 views

“[unknown]@[unknown] LOG: could not accept SSL connection: Success” in PostgreSQL logs

My PostgreSQL 10.6 server (Ubuntu 10.6-0ubuntu0.18.04.1) has the following lines in the log approx 4x per second: 2018-12-05 19:40:35.881 CET [25348] [unknown]@[unknown] LOG: could not accept SSL ...
-1
votes
0answers
23 views

missing default privileges when creating a table in Postgres

I set up default privileges for 5 users in my db: postgres (default), admin, backup, migration and user. When I create a new table from posgres everything is ok: but when I try to create a new table ...
0
votes
0answers
22 views

Combine results with same key

I have a PostgreSQL 10.6 database schema where a scenario references scenario_steps, and the scenario_steps contain a step (steps table) (referenced by step_id) and this scenario_step also contains a ...
1
vote
0answers
175 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
2answers
55 views

Why is this seemingly simple insert trigger function causing deadlocks?

This is a simplified version of my prior question. I removed the many-to-many complexity and still deadlocks. It happens WAY less frequently, but still happens. 😕 The situation... I have a tweet ...
2
votes
1answer
51 views

System configuration to address poor PostgreSQL INSERT performance

Problem Symptom High CPU usage by postmaster child process associated with client connections attempting to INSERT low-volume rows (results in rows INSERTed 25x slower than using COPY ... FROM STDIN ...
2
votes
2answers
85 views

How to prevent deadlocks in many-to-many insert/update trigger function?

I'm having a problem with deadlocks on a many-to-many insert and am pretty far out of my league at this point. I have a tweet table that receives thousands of records per second. One of the columns ...
1
vote
2answers
84 views

How to identify the column used to partition a table from the Postgres system catalogs

Given a table created as so... CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int ) PARTITION BY RANGE (...
7
votes
1answer
90 views

PostgreSQL ANALYZE execution time over 24h (still running)

I upgraded a Postgres DB 9.3.2-->10.5 using pg_upgrade (in place). I did everything according to the documentation and the instructions given by pg_upgrade. Everything went fine but then I realized ...
0
votes
1answer
33 views

Postgres jsonb complex query

I have a table on PostgreSQL 10 that looks like: CREATE TABLE forms (id serial, form jsonb); INSERT INTO forms (id, form) VALUES ( 1, '"permissions": [ {"name": "perm-name-1", "who":["group1", "...
2
votes
1answer
22 views

Serial fields at 1 after logical replication

I'm migrating my database from one server to another. So far I've done pg_dump -s and I was able to successfully transfer the schema to my new server. As for the data I went with logical replication, ...
0
votes
1answer
44 views

Avoid tables locking while doing reindex

We are using PostgreSQL 10 and we need to reindex tables to reorganize data and free space. The problem we met is that this operation locks tables that we need to remain available at all time. We ...
1
vote
1answer
44 views

Creating materialized view is very slow

I have quite a complex query in PostgreSQL 10.4 on Amazon RDS (3 joins, quite a few conditions). It takes from 1.5 to 6 seconds to perform a select. However, when I want to create a materialized view ...
-1
votes
0answers
34 views

PostgreSQL slow Select Query response for Sequential Scan

I have a table that has roughly 750,000 records. The table has 7 columns one of which is a text field where we have large text, ranging from a few hundred chars to a few KB of data. A simple select * ...
1
vote
0answers
105 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
votes
0answers
46 views

Very slow query because of bad frequency estimate

(PostgreSQL 10) I have to run a query like: SELECT id FROM tbl WHERE field1 = true AND field2 = 'x' LIMIT 200; What I know about this table is that: tbl has about 3M rows about 99% of rows match ...
0
votes
1answer
19 views

Use twice DISTINCT ON ina multiple join situation

I'm using PostgreSQL 10. I have an entity Product. The product has images, so I have a Foreign key from Product to Image. A Product have 1 ON 1 relationship, with a Video entity A product can have ...
0
votes
0answers
15 views

PostgreSQL using SymmetricDS with tokens in the initial_load_select field

I'm creating a new server test group using Postgres 9.6 & 10.3 with SymmetricDS 3.9.13. I've got a successful installation in production that syncs one database server with approx. 10 slaves. This ...
2
votes
1answer
120 views

Efficient pagination for big tables

Using PostgreSQL 10.5. I'm trying to create a pagination system where the user can go back and forth between various of results. In an attempt to not use OFFSET, I pass the id from the last row in ...
1
vote
0answers
16 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
1answer
259 views

Converting VARCHAR to TIMESTAMP with milleseconds yields “ERROR: date/time field value out of range”

I have a VARCHAR column with the value '2018-07-05T16:14:27.1427' and I convert it to timestamp with this command: select last_run, to_char(to_timestamp(last_run,'yyyy-MM-dd HH24:mi:ss') - ...
1
vote
1answer
13 views

Import Postgres 8.3 database in Postgres 10.5 [closed]

I have a database in Postgres 8.3 (and PostGIS 4.2), which I need to import in Postgres 10.5. I am a DB newbie and would appreciate any pointers to get started. Would you recommend a particular book ...
4
votes
0answers
56 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 ...
2
votes
1answer
57 views

Converting float4 to numeric in PostgreSQL is rounding the values

I have a table that has a column storing some coefficients which require high precision. The type of the column is set to float4 and I need to change it to numeric. But after altering the table, it ...
2
votes
1answer
85 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
1answer
58 views

Problem migrating PostGIS

I need to migrate Postgres 9.3 + PostGIS 2.1 to Postgres 10. I'm using CentOS 7.X Using pg_upgrade it should be quite straightforward, but the problem is PostGIS. Postgres 9.3 has PostGIS 2.1 ...
2
votes
1answer
133 views

ORDER BY ASC is 300x faster than ORDER BY DESC

The ascending sort is 300x faster than descending sort on a column which has a B-Tree index. I have table of around 20 million rows. I have indexes on inserted_at and x columns. Ascending Sort ...
0
votes
1answer
38 views

How to prevent informational messages from getting written when query results are output to a file

When I am running the below query as a batch file (.BAT) using the Windows Task Scheduler, I am getting some informative messages in the output file DatabaseNames.txt in addition to the database names....
1
vote
0answers
17 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
44 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 ...
0
votes
0answers
51 views

Not able to start the PostgreSQL Database Engine getting Event ID 0 error

I am not able to restart the PostgreSQL (10.4) database engine. Getting the below error message, "The description for Event ID 0 from source PostgreSQL cannot be found. Either the component that ...