Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

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

How to get Google Cloud SQL Auth Proxy working on Windows (WSL)?

I am trying to run Google's Cloud SQL Auth Proxy on my Windows 11 machine under WSL (Windows Subsystem for Linux). I downloaded the Cloud SQL Auth Proxy 64-bit executable and run it and get these ...
0 votes
0 answers
5 views

invalidating slot "a" because its restart_lsn 6093/6BB6F1B0 exceeds max_slot_wal_keep_size

we use google cloud sql (version is PostgreSQL 15.5), the config as follows: config, and max_slot_wal_keep_size = -1, but slot always become lost wal status (restart LSN become null) when running for ...
user avatar
1 vote
2 answers
9k views

Postgres duplicate key value violates unique constraint for autogenerated column

Inserting a row is throwing unique constraint violation on a auto-generated column when running with a bash script psql -Atx "$DB_CONNECTION_STRING" -c "INSERT INTO my_table(mt_version) ...
0 votes
0 answers
8 views

SQL Server to Postgresql migration tools

Are there any tools, preferably open source tools (but willing to take a look at paid tools also) you have successfully used for migrating SQL Server database (ranging from 100s of GBs to Several TBs) ...
2 votes
2 answers
1k 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. ...
1 vote
1 answer
22 views

What does psql do on connect (that could be taking 1 to 10 minutes)?

In some cases psql is taking ~1 to 10 minutes to connect to PostgreSQL, with no queries that it seems to be running while doing so (according to pg_activity that I think queries pg_stat_activity) I'm ...
1 vote
2 answers
520 views

Is it possible to COPY tables into PostgreSQL without setting structure first?

I'm just getting to grips with PostgreSQL 9.6. I'm wondering if there is a simple way to add a .csv table to a database without first creating the table structure in psql or pgAdmin? Or...I've not ...
2 votes
2 answers
24 views

Does a serializable transaction around a single statement have any effect compared to an implicit transaction?

I'm doing some performance auditing of our codebase and have noticed that we always run SQL statements within a serializable transaction. I've also noticed that many of these transactions only perform ...
2 votes
1 answer
275 views

How to (pg_)restore selected tables with all indexes?

I would like to restore selected (currently after -t) tables including all indexes without making a definition after -I - because indexes are changing, new ones are created, old ones may expire. What ...
9 votes
1 answer
2k views

AWS RDS postrgres massive disk use, small tables

I can't figure out why our AWS postgres server has consumed all of it's space. We just had to up the storage space allocated to it, but can't find any hint from postgres that it's using that much ...
1 vote
1 answer
275 views

How indexes work in composite key and how changing the order affects the performance in Postgres 12?

CREATE TABLE public.ABC ( it_id TEXT NOT NULL, item_id varchar(50) NOT NULL, PRIMARY KEY (it_id,item_id) ); When I use above query then index scan is applied when i query only by it_id or ...
0 votes
3 answers
60 views

What index can be created to optimise this query?

I've got the below SQL query that runs extremely slowly. As for this query, this is due to the "ORDER BY" statement, since Postgres is scanning the changes table by "counter" which ...
1 vote
1 answer
2k views

Postgres constraints: EXCLUDE (name WITH =) vs partial unique index

I am refactoring my database constraint for such a table: CREATE TABLE products ( name text NOT NULL, status text NOT NULL ); A former engineer working on the project had such a constraint: ALTER ...
0 votes
1 answer
458 views

Can I run commands from aws_s3 extensions as non-root on PostgreSQL?

I'm trying to copy data from S3 to PostgreSQL (Amazon AWS RDS). I've set up the following: CREATE EXTENSION aws_s3 cascade schema reporting; CREATE DATABASE my_database; \connect my_database; -- ...
0 votes
1 answer
16 views

Postgresql: could not determine polymorphic type because input has type unknown

The following query works: select t.oid, t.typname, case t.typcategory when 'C' then json_object('{}') when 'E' then (SELECT array_to_json(array_agg(enumlabel::text)) FROM pg_enum WHERE ...
1 vote
1 answer
2k views

DB Design - Assigning users to multiple sites with different roles

I am working on a DB design that lets me manage roles and permissions for each user per site. These are my business rules: A user can manage multiple sites A user can only have one role per site A ...
0 votes
1 answer
426 views

PG Admin Sessions

I have a frontend written in MS Access that connects to a Postgres backend through ODBC. Sometimes the number of sessions reaches 100 and at that point I get an "ODBC call failed" error. I ...
-1 votes
2 answers
31 views

LEFT JOIN LATERAL with a function result, how to prevent unnecessary function calls?

I have a costly function and it only provides relevant data when certain conditions are met. That data is inserted into the result through a LEFT JOIN LATERAL as shown below. As it works now, the ...
-1 votes
0 answers
17 views

postgreeSQL performance estimation w.r.t reads and writes per second

Here are all the parameters involved:- instance (aws rds db.t3.micro):- vcpu:2,memory:1GiB, 1 core Image :-PostgreeSQL 15 Tables:- TableA:-(aid (PK),a1(FK to B.bid),a2,..a7) TableB:-(bid (PK),b1(...
0 votes
1 answer
44 views

Postgres doesn't use functional index for LIKE queries

I have a query with a LIKE operator: SELECT "task_v1_taskv1"."id", "serviced_object_v1_servicedobjectv1"."id" FROM "task_v1_taskv1" ...
1 vote
1 answer
19 views

Is it possible to change the sequence backing an identity column?

After a machine malfunction and a hurried transfer of data onto another machine, we have somehow ended up with some brand new sequences replacing old sequences as the backing for identity columns (and ...
1 vote
1 answer
867 views

SELECT FOR UPDATE (NOWAIT) on auxilliary table for serializing updates?

Background: I have an inventory tracking application which needs to avoid the following sort of situation where the same inventory might be used twice due to lack of serialization. The constraint is ...
10 votes
2 answers
8k views

Are results from UNION ALL clauses always appended in order?

As per standard SQL UNION / UNION ALL do not guarantee any particular sort order without an outer ORDER BY clause - like there is hardly any place in SQL where sort order is guaranteed without ORDER ...
0 votes
0 answers
10 views

Inheritance in Postgresql and column key references

What happens to downstream rows depending on a PK (via col id) when that PK row changes ID, but not it's value? Think a row deleted and recreated, etc.) There is a post here that I was reading for ...
0 votes
1 answer
46 views

Extremely slow query whenever using ORDER BY, even when an index is present

I've been trying to debug a particularly slow query that never completes (it takes forever and eventually timeout), and found out that it's down to the ORDER BY statement: if it's there, it never ...
0 votes
0 answers
28 views

How to query for aggregated streaks?

My goal is to detect price trends for cryptocurrencies. For that I'm getting OHLC pricing data for multiple currencies vs. multiple quote symbols (e.g. candles for Bitcoin/USD, Bitcoin/EUR, Litecoin/...
5 votes
1 answer
6k views

Postgres Streaming Replication: How to re-sync data in master (with standby) after failover

I am absolutely new to postgres 9.1 replication mechanisms. I have managed to set up streaming replication between a master and standby postgres (Both have x number of records). At one point when ...
0 votes
0 answers
21 views

What kind of hardware / software architecture would be preferable for petabyte size data storage and replication? [closed]

For an upcoming feature, I got the task to develop a solution that would store data for years in a separate machine (let's call it master), while also replicating for disaster recovery. I'd like to ...
0 votes
0 answers
8 views

pgBackRest - optimal policy / setup for RPO of 1 hour

I need a bit of advice. I have a postgres 14 database and a requirement for the DB, to be to recovered with RPO of 1 hour (acceptable data loss is max of 1 hour). Database size is 300 GB, backup size ...
0 votes
1 answer
276 views

PostgreSql subquery evaluated without filtering

I am struggling to optimize the following query: SELECT tbA.id, tbB.createDate FROM tbA LEFT OUTER JOIN (SELECT id, MAX(createDate) as createDate FROM tb GROUP BY id) as tbB ON tbA.id=tbB.id WHERE ...
3 votes
3 answers
3k views

Create user to monitor database activity on Postgres

Postgres 9.3 Debian 7 I want to create a monitor user called monitor that the only permission is to run queries to monitor all the database activity. I created the user and it can select from ...
1 vote
1 answer
1k views

"merge" two tables -from different databases- together?

Well say I have a table running on a database at localhost (donor database A), and a receiving table running at another server externally (database B) With table creation (on both sides) like: CREATE ...
0 votes
0 answers
18 views

postgresql query IllegalArgumentException in Yellowbrick DB

I'm writing Pl/pgSQL joining query in Yellowbrick DB. I have join 6 tables at a time. It was working fine. Suddenly I observe my query is throwing error: "IllegalArgumentException" Checked ...
2 votes
2 answers
191 views

How to make a Data Modifying CTE abort if an update only partially succeeded?

I am wondering if it is possible to add some kind of check to make a data modifying CTE abort if an update is partially successful. Attached is a minimal query that represents the problem. I would ...
4 votes
2 answers
19k views

In Postgres, how do I adjust the "pgsql_tmp" setting?

I"m using Postgres 9.5.4 on Ubuntu 14.04. I have my Postgres data on a separate disk partiion, which is getting pretty full. myuser@myproject:~$ df -h /mnt/volume-nyc1-01/ Filesystem Size Used Avail ...
0 votes
0 answers
5 views

psql psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: database "postgres" does not exist

I have PostgreSQL installed on WSL. The PostgreSQL server is running, but I'm getting an error when I type psql. sudo -u postgres psql psql: error: connection to server on socket "/var/run/...
1 vote
1 answer
282 views

How to optimize a PostgreSQL table for heavy writes, yet still maintaining atomicity?

There are plenty of guides online on how to speed up writes on a PostgreSQL table, from tweaking the WAL configuration, turning off synchronous_commit, to creating unlogged tables. I'm currently ...
0 votes
2 answers
2k views

Join multiple tables without duplicated results and without UNION in PostgreSQL

Lets assume we have two tables A and B in PostgreSQL: A title first_group_id second_group_id My Title 1 2 B group_id element_name 1 First 1 Second 1 Third 2 Fourth 2 Fifth I want to get the ...
22 votes
3 answers
157k views

Postgres: How to insert row with autoincrement id

There is a Table "context". There is an autoincrement id "context_id". I am using sequence to retrieve the next value. SELECT nextval('context_context_id_seq') The result is: 1, 2, 3,...20.... But ...
0 votes
1 answer
13 views

granting privileges to user in postgresql

I'm trying to setup a new project with a Postgresql 15.5 database. I have a running instance. If I create a new database psql --command "create database \"tagger-db\"" create a ...
0 votes
1 answer
280 views

What are my options for replicating a Postgresql database over an unreliable connection?

I have a postgresql database that I want to replicate in a master/slave configuration. But the slave is in a remote location with unreliable internet (as in it could go down at any time for seconds to ...
9 votes
4 answers
24k views

Postgresql extremely slow count (with index, simple query) [duplicate]

I need to run these simple queries on a table with millions of rows: SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123; SELECT COUNT(*) FROM "subscriptions" WHERE "...
0 votes
2 answers
183 views

Is it possible to do WAL physical replication to a db without making that db read-only?

(Novice question. Please let me know how I can improve the question, or if I need to delete) I want to create a separate database so I can run more expensive queries on it. This database should be a ...
0 votes
1 answer
28 views

DBMS for multiple DB Enviroment Management

I have moved into a DBA role where I now manage multiple types of DBs. I used to manage only MS SQL and MySQL, but now I am managing MS SQL, Azure SQL, MySQL, MariaDB and PostgreSQL. I am currently ...
2 votes
1 answer
96 views

How to catch a particular exception?

It is possible to catch an exception in plpgsql: EXCEPTION WHEN unique_violation THEN But how to check for a violation of a particular constraint? The following example creates two keys: one for a ...
0 votes
0 answers
16 views

Crunchy Postgresql Backup configuration

I am trying to set up a backup for minio S3 but failing on the job I would say that it probably a certificate issue I am using Gardner cloud and I have S3 storage provided from the cloud provider via ...
0 votes
2 answers
4k views

Is there a way to search the earliest or latest postgres partition for table?

BACKGROUND I am working on a web application that has date pickers for searching. Those searches query a partitioned table. So far, so good. However, based on the data I'm querying, we don't have a ...
0 votes
0 answers
24 views

Need an accurate way of comparing FLOAT datatypes from two different database architectures

I am doing a data load from MS SQL Server into Aurora PostgreSQL. Many of our tables use the float data type (whether or not this was a wise decision is outside the scope of this post, since the ...
2 votes
1 answer
3k views

Connection to AWS database gives back "You may not specify an IPv4 CIDR for an existing referenced group id rule."

I try to connect to an AWS database instance from a client by following this tutorial: https://medium.com/simplebackups/allow-a-remote-ip-to-connect-to-your-amazon-rds-mysql-instance-2d5074d601eb I ...
1 vote
0 answers
12 views

Manual Grant permission from psql prompt is working, however the same Grant query is not working from bash shell script [closed]

I have a PostgreSQL_15 database user, say 'admin', who is member of rds_superuser group and database name is postgres. There is another user, say 'normal_user' associated with another database say '...

1
2 3 4 5
338