Questions tagged [postgresql-12]

Specifically for PostgreSQL version 12

Filter by
Sorted by
Tagged with
0
votes
0answers
12 views

Trigger PostgreSQL replication

Is it possible in Postgresql 12 to set up master/slave servers so that the replication has to be manually triggered? The database is large, so it’s not feasible to dump and import. The optimal would ...
0
votes
0answers
21 views

I need a script to cycle all tables in postgres to analyze if needed

I need a script to cycle all tables in postgres to analyze if needed. Something like: create or replace PROCEDURE analyze_all_tables () language plpgsql as $$ declare q TEXT; r RECORD;...
0
votes
1answer
15 views

postgresql copy comand with date in filename

I have a query and want the output in a .csv file with today's date and time in the name (I am using PostgreSQL v12). COPY (SELECT * FROM mytable) TO 'C:\Users\me\mylocation\filename_date_time.csv' ...
1
vote
1answer
16 views

TimescaleDB distributed setup strategy

What is the best setup strategy for TimescaleDB? My initial thoughts based on the TimescaleDB FAQ was that it is indeed recommended and possible to basically have one database that contains both time ...
1
vote
1answer
18 views

Postgres COPY with on conflict ignore - possible?

I want to add on conflict ignore to the Postgres copy command. I know I can copy the data to a table without unique index /primary key and then use insert with the on conflict syntax. But I wanted to ...
-1
votes
1answer
27 views

if multiple users run a query that creates a temp table at the same time, would the data in the table be corrupted in each session? [closed]

I am using postgres12. I have a query that creates a temp table named table_x and returns it. I am wondering if this data will be corrupted since there is multiple users running the same query at the ...
0
votes
0answers
40 views

PostgreSQL extended statistics

I have a 1 TB read-only database where performance is critical. It's difficult to predict queries since they are dynamically generated by the users (the whole thing is basically a visualization ...
-2
votes
0answers
47 views

Why is the restore failing? [closed]

i can't see the database tables i just backed up. I don't understand what the problem is, do I have to enter the password? But when I create the backup copy, it doesn't ask me for the password. I ...
0
votes
1answer
66 views

Using function is much slower than running query

I am trying to write a plpgsql function that runs a query where the WHERE part is variable using EXECUTE, like this : CREATE FUNCTION test1(p_filter TEXT) RETURNS SETOF Bin AS $$ DECLARE q ...
-2
votes
3answers
60 views

Is there really no SIMPLE way to achieve “recursive lookup” in PostgreSQL?

I have a table of categories: id,label,parent_id 1,Animals,null 2,Dogs,1 3,Cats,1 4,Black,3 This means that "Animals" is a top-level category, and "Dogs" and "Cats" are ...
1
vote
1answer
26 views

How to resolve “logical replication target relation schema.table is missing some replicated columns” error in postgresql logical replication?

I'm getting "logical replication target relation schema.table is missing some replicated columns" error in logical replication. I'm using all table replication. Due to some changes I ...
0
votes
1answer
23 views

pg_restore importing single table to a file

I have a backup in directory format taken with pg_dump. When I try to export single table from this dump with the command: pg_restore -Fd mydirectory -t sometable > table.sql I get the error : ...
0
votes
1answer
26 views

Geospatial join using ST_Contains or ST_Within seems slow, with costly query and bad estimate

Problem I have two tables, one which which includes a point column and the other which includes a boundary (table definitions below). I want to select rows from the first column where the point is ...
0
votes
2answers
45 views

Unpacking JSON into 14 columns without crosstab() and tablefunc?

In a Postgres 12 database, I have a simple schema containing a JSON column. The JSON will always contain an object, and inside the object there are always 14 keys like so: CREATE TABLE json_unpacking ...
1
vote
1answer
51 views

INSERT with ON CONFLICT does not insert rows as expected

We have two tables: CREATE TABLE listing_master ( id SERIAL PRIMARY KEY, feed_id character varying(20) NOT NULL, feed_listing_id character varying(20) NOT NULL, updated_time timestamp(...
0
votes
0answers
7 views

Postgresql table lock to update constraint

I have a table in PostgreSQL 12. Some data are not formed properly so I need to introduce a check constraint so it doesn't happen again. I have a running system that updates this data continuously and ...
0
votes
1answer
40 views

Drop tables but space not claimed in postgres 12

I have upgraded Postgresql 9.5 to Postgresql 12.4 a few days back using pg_upgrade utility with link (-k) option. So basically I am having two data directories i.e. One is old data directory (v9.5) ...
0
votes
3answers
40 views

Postgres 12 drastically slower than postgres 9.5 with identical queries from import/export

I setup a new postgres database (v12) by export/importing from an old one (v9.5). When I query the new one, it's drastically slower despite being on a more powerful machine. Shared buffers are set to ...
0
votes
0answers
17 views

How can I write a query avoiding intermediate views/steps from different tables in PostgreSQL?

I am working in a large query I would like to eliminate intermediate steps, so I am trying to write the two queries below in just one. The first query (QUERY 1) select the grid id from a table called ...
1
vote
2answers
74 views

Optimizing postgres sorting by word_similarity

I have a table with 2M rows of hotel names in Postgresql 12. I am building a typeahead so users can quickly find the hotel by name. I have played around with different Postgres options including FTS, ...
1
vote
1answer
34 views

REPMGR - change reconnect_attempts and reconnect_interval not working

I configured PostgreSQL automatic failover with REPMGR. But, in my simulation the sleeping time is 1 second for each reconnect attempt: If I consult official documentation the default value to ...
0
votes
3answers
35 views

Sort varchar by its numeric fields

I'm sure this has been asked before, but since the question can be formulated in many different ways it's difficult to find the proper answer. I have an orders table with a varchar field for the order ...
0
votes
0answers
12 views

How do I enable pgadmin4 (PostgreSQL12) to read and write files in directories of other Linux Users on RHEL8/CentOS8 [duplicate]

While trying to export a query result set from PGAdmin4 on PostgreSQL12 to a CSV I am encountering the following error: ERROR: could not open file "/home/linuxusername/Downloads/data.csv" ...
0
votes
0answers
37 views

CentOS 8 Unable to connect to PostgreSQL 12 Server

When I attempt to connect to postgres in from my linux command line, I receive the following error: [lloyd@localhost ~]$ sudo -u postgres psql could not change directory to "/home/lloyd": ...
0
votes
1answer
22 views

In Postgresql 12 how to I make the primary server a slave after a deliberate failover

I am using PostgreSQL 12 on Debian 10.6. I have been reading up about replication and I have set up an asynchronous replication between one primary and standby. I wanted to test failover so I stopped ...
0
votes
1answer
18 views

How can I manage to view if a table has been read recently in postgresql 12?

How can I manage to view if a table has been read recently in postgresql 12 ?
0
votes
0answers
117 views

PostgreSQL 12 problem restoring from pg_basebackup on CentOS8

I have recently created a Postgres backup, and attempted to use this backup, at which point I recieve the following error, when I try to start the server with - start postgresql-12.service, I get : ...
0
votes
1answer
12 views

Join on nested obj attribute in JSONB column along and a varchar column

I have table connections where both columns are varchar: +---------+---------------+ | user_id | other_user_id | +=========+===============+ | foo | bar | +---------+---------------+ | ...
5
votes
1answer
135 views

How to predict how much space a VACUUM FULL would reclaim?

Is there a way to determine in advance how much disk space a VACUUM FULL on a particular table would return to the OS? Therefore you can decide whether it's worth the cost in doing so. Bonus if there'...
0
votes
0answers
19 views

Performance issue in Postgresql 12

It is a Java and Hibernate based application that processes rows in a table. The application when launched loops through rows in a table, executes anywhere from 10-25 queries per row and inserts ...
0
votes
1answer
34 views

Unable to delete a referenced row due to a trigger on a referencing table?

I ran into some problems in using triggers to ensure only deletions from cascading deletes: CREATE TYPE status_type AS enum ('A'); CREATE TABLE parent( name VARCHAR(255) PRIMARY KEY ); CREATE ...
0
votes
1answer
127 views

Get the last ID after INSERT the PostgreSQL database with PgBouncer

Previously we used PostgreSQL 9 with PgBouncer in session mode, and with that was able to easily create a new record and get the last ID with pg_get_serial_sequence and CURRVAL. INSERT INTO "...
3
votes
1answer
49 views

Is there a way to allow only DELETES from CASCADES only?

I want to prevent anyone from deleting certain records from a table, except for when the parent record in another table gets deleted, then should it be deleted. Can I use triggers for this purpose?
0
votes
1answer
21 views

Reindex or Analyze Tables on PostgreSQL after import

I used PostgreSQL 9.5 on a server and I ended up migrating the data to a new server using PosgreSQL 12. Should I perform a REINDEX for all tables or just ANALYZE for all tables?
0
votes
1answer
61 views

When I set Datestyle to 'SQL, YMD' all queries still return as 'SQL, MDY'

I am working on a database migration from Oracle 12c to Postgresql 12. We must keep the same date format as the program interacting with this database is written in COBOL and should not be changed. ...
0
votes
0answers
73 views

pgpool-II and Postgres docker image : automated failover and online recovery via rsa key

I've been following this documentation for pgpool-ii https://www.pgpool.net/docs/latest/en/html/example-cluster.html I'm having a hard time setting up rsa on my postgres streaming cluster built in ...
1
vote
2answers
77 views

Postgres Upgrade Issues with pg_upgrade

I am trying to upgrade 9.5.5 to 12.3 using pg_uprade, got the following error Old and new pg_controldata maximum relation segment sizes are invalid or do not match 9.5.5 Blocks per segment of large ...
0
votes
0answers
36 views

postgresql server, can connect with SSL, permission denied

I installed and used a Postgresql server a while back and have been using it with Postgis. I would like to use my letsencrypt certificates with the server so i followed this article: https://medium....
0
votes
1answer
28 views

Creating and dropping partition in PostgreSQL “on the fly”?

In our web-applicatons we have PostgreSQL database. Users write into and delete from this database ecology forecasts. Because data amount is significant (more than 100 Gb), we use declarative ...
0
votes
1answer
37 views

How can I determine what's a better configuration for my app? [closed]

I'm currently using Postgres 12 on AWS RDS and am using their default configuration for the database. I use a gem called PgHero that advises me to use PGTune to improve the efficiency of the database. ...
2
votes
2answers
51 views

nulls on pg_stat_get_progress_info('VACUUM') columns on pg_exporter role

I'm trying to export the results of a query select * from pg_stat_progress_vacuum;. The problem is that the only column values I can see from the created user are: pid datid datname Other columns ...
0
votes
1answer
95 views

PostgreSQL - Check value is not the same in multiple columns

I have a table to hold user information and is used as part of the login/registration process. Part of the requirement is to allow the user to login using either their username or email address. A ...
0
votes
0answers
28 views

PL/PGSQL - Inner Join in Foreach loop

So I have a table, report_items with a structure as follows: id (uuid) | date (date) | sheet (text) | report_item (text) | report_value (numeric) | stock_id (uuid) | period (text, either quarterly or ...
1
vote
1answer
33 views

PLPGSQL: Cannot use parameters inside a function query

I'm trying to create a function that returns all events with the corresponding week number, and the day of week the event is occurring using crosstab and series generator. I have tested that the ...
0
votes
1answer
35 views

Prevent consecutive duplicate values without a trigger

Within a group, I'd like to prevent INSERTs of consecutive duplicate values, where "consecutive" is defined by a simple ORDER BY clause. Imagine a set of experiments which is regularly ...
0
votes
1answer
102 views

Create GIN index in a schema with the pg_trgm extension

I'm migrating entities from the public schema into a new one, let's call it myschema. Originally, the pg_trgm extension was installed and the index was created in a schema migration with: CREATE ...
1
vote
2answers
40 views

Using NEW on tables with generated columns

I have a PostgreSQL 12 database, and in a table I created a column with a generated column, then I have a before update trigger that fires only if the NEW value is DISTINCT from OLD value, essentially ...
0
votes
1answer
128 views

PostgreSQL CPU 100% after configure Wal log shipping hot standby

I have configured Hight Avaliabily WAL Log Shipping Hot Standby (SSL) with one node. But after I configure, both hosts (primary and secondary) take 100% CPU usage. The most strange is because I have ...
0
votes
1answer
228 views

invalid integer value “CONNECTION” for connection option “port” Previous connection kept

I'm new to postgres and I'm trying to configure table level logical replication(PUBLICATION/SUBSCRIPTION) on postgres-12. I have updated postgresql.conf file and updated wal_level=replica. Also, then ...
4
votes
1answer
72 views

VERY slow lateral join on relatively small database

My database consists of apps and their reviews (schema below). I'm trying to answer the following question: Given a series of dates from the earliest reviews.review_date to the latest reviews....