Questions tagged [postgresql-12]
Specifically for PostgreSQL version 12
126
questions
0
votes
1answer
18 views
How To Make Replication From PostgreSQL To SQL Server [closed]
I have a question and need your opinions on the subject. I have a PostgreSQL and a SQL Server database server and i want to replicate the servers with each other from PostgreSQL to SQL Server. Have ...
0
votes
1answer
25 views
Unable to connect to postgres inside a docker container
I've started a new postgres:12 docker instance as:
$ docker run -it -d --name newdb \
-p 5444:5432 \
-e POSTGRES_PASSWORD=postgres \
-e POSTGRES_USER=postgres \
--volumes-from dbstore \...
0
votes
1answer
27 views
Conditionally insert a value in an array when creating a new column
I have some data in a jsonb col that I need to clean and insert into another text array column.
What I have:
[
{
"v": "birthday"
},
{
"v": "reference&...
1
vote
4answers
34 views
PostgreSQL query takes a long time (as it's not using the index)
This query takes a long time, as I guess it's doing a full-table scan. It works ok in Oracle, just not in Postgres 12.
select count(*)
FROM EVENT
WHERE PK_EVENT > (select CHECKPOINT1_PKEVENT FROM ...
0
votes
1answer
16 views
which is the best method to upgrade postgresql from v10 to v12 ( upgrading OS also from Ubuntu 18 to Ubuntu 12 OS)? [closed]
We are planning to migrate Postgresql from v10 (which is on Ubuntu 18) to v12 ( which is on Ubuntu 20)
as Postgres 12 has many new features, which is the best method to upgrade.
Method 1:
Backup DB,
...
0
votes
1answer
69 views
How to prevent pg_dump from generating “SET default_table_access_method=heap;”?
I have a big dump file that is generated by pg_dump that comes with PostgreSQL v12 client, and it is in customized format instead of plain sql text. The version of the server is PostgreSQL v12 too.
...
1
vote
0answers
19 views
PostgreSQL 12.5/ pgAdmin - postgis. Error: column p.proisagg does not exist [duplicate]
pgAdminIII reports errors related with a column missing after installing postgis in postgres 12.5 and ubuntu 20.04
Error column proisagg does not exist where perhaps you meant pr.proisagg
people ...
0
votes
0answers
13 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
27 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
28 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
21 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
67 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
29 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
1answer
46 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 ...
0
votes
1answer
68 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
70 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
30 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
35 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
29 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
62 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
54 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
43 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
50 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
18 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
94 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
38 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
40 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
44 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
33 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
205 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
14 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
140 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
21 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
177 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
24 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
90 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
142 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
101 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
51 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
31 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
68 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
60 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
162 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
32 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
40 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 ...
