572 questions
0
votes
1
answer
57
views
Query performance does not improve when storing sorted data
I am trying to improve the performance of the base using the cluster command. For tests, I created copies of the original tables and sorted the data in them by foreign keys, by which the most frequent ...
- 1
2
votes
1
answer
86
views
Why am I getting "collation attribute X not recognized" in my PostgreSQL database?
I'm running a PostgreSQL 11.22 database, and I want to create a new collation to support the Czech language, since I don't see it on the collations list when I select from pg_catalog.
Attempt 1
The ...
- 31
0
votes
0
answers
105
views
Do Aurora Serverless v2 supports Foreign Data Wrapper (FDW)?
Currently I'm using Aurora Serverless v1 which don't supports FDW(foreign data wrappers). I need to setup FDW for some requirement. I just want some ways to tackle this situation.
1.First approach may ...
1
vote
1
answer
95
views
PostgreSQL: Why "select into" is faster than "select count" from the same sourcetable?
After upgrading PG from 11 to 15, I struggeling with the following situation:
old (working) Setup:
Postgres 11 PostGIS 2.5
Setup:
Postgres 15 PostGIS 3.3
table_1 (~14.000.000 rows)
When I do select ...
- 72
0
votes
1
answer
254
views
Error during pg_upgrade Process: 'database is being accessed by other users'
During the pg_upgrade process, I encountered an error indicating that the "postgres" database is being accessed by other users, with one active session identified.
pg_restore: error: could ...
user19868155
0
votes
1
answer
4k
views
"fe_sendauth: no password supplied" During pg_upgrade Process
I'm encountering an error "fe_sendauth: no password supplied" while attempting to run pg_upgrade to migrate from PostgreSQL 11 to 14. I've set up a .pgpass file with the password and ...
user19868155
1
vote
2
answers
977
views
Postgres 11 not enforcing foreign key constraint?
Our PostgreSQL 11 database has tables A and B.
postgres=# \d A;
Table "public.A"
Column | Type | Collation | Nullable | ...
- 3,436
0
votes
0
answers
83
views
Postgres query taking too much time on the first run
I have a a query which takes too much time on the very first run, and I need help reducing the time. It has too many large tables involved and has an index on all the columns involved in joins/where ...
- 1
2
votes
0
answers
548
views
Postgres Upgrade from 11.16 to 14.4
We are trying to upgrade our AWS RDS postgres version 11.16 to max allowed latest version 14.4
Now that we are skipping and jumping directly to 14. Is it a good idea? any issues we should look out for ...
- 429
0
votes
0
answers
192
views
Taking incremental pg_dump since last restore
We are developing a large application and all our tests are based on a reference Test DB. This test DB is initially loaded by executing different scripts and you can add your own script to add more ...
- 5,504
0
votes
1
answer
975
views
How to pass an array of tuples as parameter for a bulk UPSERT?
With this schema:
CREATE TABLE tag (
tag_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
tag_slug text UNIQUE NOT NULL,
tag_name text NOT NULL
);
I'm currently using Javascript to generate ...
- 4,642
0
votes
1
answer
151
views
Finding records which might indicate fraudulent activities with SQL
I have following tables:
CREATE TABLE IF NOT EXISTS auth_user
(
id SERIAL PRIMARY KEY,
username VARCHAR(30),
first_name VARCHAR(30),
last_name VARCHAR(30)
);
CREATE TABLE IF NOT ...
- 3,029
1
vote
2
answers
2k
views
How to initialize PostgreSQL database directly within a Dockerfile?
Is there a way to initialize/start/keep running a PostgreSQL DB directly from a Dockerfile? I have the following Dockerfile.
# syntax = docker/dockerfile:1.4.0
FROM redhat/ubi8:latest
# Install a ...
- 3,119
2
votes
1
answer
602
views
How to fix 'type abstime does not exist' error when upgrading PostgreSQL from 11.20 to 12.15?
Trying to upgrade PostgreSQL from 11.20 to 12.15 and hitting the below error
pg_restore: error: could not execute query: ERROR: type "abstime" does not exist
I do understand that Data types ...
- 23
0
votes
0
answers
326
views
Query against Postgres runs forever over JDBC, but finishes in 1 Minute through pgAdmin
We have a pgsql function that inserts rows into the db based on a complex query. The database is postgres 11, on azure single server (16 cores, 32gb memory). The last problematic run would insert 6000 ...
- 5,069
0
votes
1
answer
71
views
Conditional constraint if scheme and table exists in PostgresSQL
I am having multiple scheme defined, and while table creation I need to add conditional constraint that the constraint should only be added if the table and scheme exists in PostgresSQL. Below is the ...
- 1,583
2
votes
1
answer
143
views
How to send a NULL argument through a DirectFunctionCall?
I'm modifying the function Datum create_vlabel(PG_FUNCTION_ARGS), within Apache AGE.
But sometimes it's needed that one of the arguments to this function be NULL, and there's a check to that, like ...
- 221
0
votes
1
answer
1k
views
create a user mapping without password
I am trying to create a user mapping in PostgreSQL without a password, but I am encountering an error that says "ERROR: password is required" when I try to access a table. Here is the ...
- 125
0
votes
1
answer
585
views
upgrading postgres 11 to 13 fails in consistency check
when i try to upgrade Postgres from version 11 to version 13 using this command:
/usr/lib/postgresql/13/bin//pg_upgrade
-b /postgresql/11/bin/
-B /postgresql/13/bin/
-d /data/service/postgres.1061/...
0
votes
0
answers
25
views
How can I open cmd in PostgreSQL?
I know, that it possible to write python code in postgresql with PL/Python Functions.
Is it possible to call cmd command from postgresql? I need to use pg_repack, not from terminal as usual, but from ...
- 147
0
votes
1
answer
375
views
use postgresql image with icu collation in circieci (cimg/postgresql, rails) [duplicate]
I am using cimg/postgres as my database image.
- image: cimg/postgres:11.12
environment:
POSTGRES_HOST_AUTH_METHOD: trust
POSTGRES_DB: XXXX
POSTGRES_USER: XXXX
Then I'd like ...
- 131
0
votes
0
answers
336
views
old and new data directory size of postgresql does not match
I am trying to update PostgreSQL 11.2 to PostgreSQL 14.0.
My PG 11 Data directory size is 1.1 GB and my PG 14 data directory is 41 MB before the upgrade.
I am now running my below upgrade command:
&...
- 204
0
votes
1
answer
232
views
Can't start server on PostgreSQL 11.17 - WSL
I have postgresql-11.17 source code on my windows, followed the installation from the documentation : PostgreSQL-11 Documentation but when I run the command ./pg_ctl -D /usr/local/pgsql/data -l ...
- 715
0
votes
1
answer
172
views
Revoke grant with dblink in Postgres
I start to use dblink on my postgreSQL 11 db and I have a problem with grant. I can create server and user mapping as superuser , but also I can delete this user mapping and server as usual user. I ...
- 147
1
vote
1
answer
1k
views
pg_restore not restoring sequences value
I have 2 databases : dbprod and dbdev, with 2 users uprod and udev.
What I'd like to do is to copy the production database to my development database.
I succeeded to restore my database, but for some ...
- 321