684 questions
0
votes
0
answers
26
views
Automatically transfer all data from postgres server to another remote server [migrated]
I need to synchronize a test environment with production data (n8n worflows, user info, etc), both have postgres databases and reside on the same intranet
(e.g: server A: 10.0.0.10, server B: 10.0.0....
2
votes
0
answers
86
views
pg_dump fails while taking backup from Dbeaver [closed]
I take backup from Dbeaver for my Postgres production database on daily basis. there are three schemas in it: two client and one workflow schema. the size of client databases are larger than that of ...
- 21
0
votes
0
answers
119
views
Why Postgres 17 cert authentication fails in Windows?
Steps to reproduce:
Install Postgres 17.5 and OpenSsl on Windows 11
Run the following commands.Enter postgres as common name on client cert creation:
openssl req -new -x509 -days 365 -nodes -out ...
- 28.2k
1
vote
1
answer
85
views
Why is my custom function inside pg_catalog not included in pg_dump or pg_upgrade?
I created a custom SQL function inside the pg_catalog schema in PostgreSQL. However, when I run pg_dump or perform a pg_upgrade, the function doesn't get included in the dump or upgraded cluster.
Here’...
- 41
0
votes
0
answers
41
views
gcloud sql export --parallel gives "invalid byte sequence for encoding "UTF-8"" when restoring with pg_restore to local postgres database
I ran an export on my database with
gcloud sql export sql "${INSTANCE_NAME}" "${GCS_BUCKET_URI}/${INSTANCE_NAME}_${TIMESTAMP}" --database=postgres --offload --async --clean --...
- 11
0
votes
1
answer
504
views
Postgres restore tables from a dump into a different schema
I have a database with two schemas, say development and production. Now I dumped a few tables from schema development and wanted to restore the dumped tables into schema production. I thought it ...
- 1,347
0
votes
0
answers
77
views
How to dump/restore a subset of tables in Postgres without disrupting FK references?
My Postgres database has many tables. Our web application allows administrators to update some of the tables and normal users to update others.
The user-managed tables have some foreign keys to rows ...
- 271
0
votes
0
answers
150
views
upgrade of Postgres 12.3 /Timescaledb 2.5.1 , very large database of 30TB to PG 16.2 /timescaledb 2.14
We have a very large Postgres database on version 12.3 with timescaledb version 2.5.1.
Size of db ~30 TB
**# of entries in _timescaledb_internal = ~1.7 million. (chunks for historical data).
**# of ...
- 1
0
votes
0
answers
611
views
How to edit toc.dat file without compromising binary?
I am looking for a way to properly edit a toc.dat file generated by a pg_dump.
The idea is to rename the schema's name before the data import with pg_restore.
pg_dump -j 2 --host localhost -p 5433 -Fd ...
- 1
0
votes
1
answer
606
views
Pg_restore runs ALTER TABLE .. OWNER TO ignoring pg_dump --no-owner option
I'm using an mac mini m1 and postgresl14 on it for a database migration.
For the migration a change of the owner is necessary.
To export the dump needed I log in on my mac, call sudo su to become root ...
- 47
0
votes
0
answers
49
views
Rollback pg_restore result
Maybe somebody can help me with the issue regarding pg-restore. I have restored database from pg_dump with the command below
/usr/lib/postgresql/10/bin/pg_restore --host=host --username=username --...
- 1,784
0
votes
1
answer
107
views
does pg_dump provide a consistent value for sequences in postgreSQL (for long running transactions)
When pg_dump captures a full backup, if we have sequences being updated, does pg_dump guarantee that the sequence values will be consistent when the export is being done? To elaborate, if pg_dump ...
- 1
0
votes
2
answers
119
views
Postgresql 16.2: ERROR: could not find hash function for hash operator 607
I've problem with my database running on postgresql 16.2. My application is running fine, it can query data, but I'm not able to create a backup using pg_dump or inspect database via psql.
Running ...
- 1
1
vote
2
answers
867
views
database pg_dump from k8s pod in directory format
I need to create a Postgres database dump using pg_dump. Database located in Kubernetes pod. I am trying to get dump in directory format, not just single .sql file. But I don't understand how can I ...
- 1,784
3
votes
1
answer
468
views
Why does pg_dump add extra comments for public schema?
When I run pg_dump -d <database_name> --schema-only --no-privileges --no-owner --no-comment --file db/structure.sql it adds these lines to the dump:
--
-- Name: public; Type: SCHEMA; Schema: -; ...
0
votes
0
answers
165
views
ERROR: syntax error at or near "\" when running sql script via psql | pgAdmin | PostgresSQL
I'm facing an issue while performing a pg_dump of an existing database into two SQL files.
One file is intended for a schema-only dump.
The other is for a data-only dump
The command for the schema-...
- 33
0
votes
1
answer
669
views
Need some clarification on dropping database during pg_restore
When using pg_dump with the clean option, it generates SQL statements tailored to drop objects when restoring with pg_restore (pg_dump custom format file contains 'DROP DATABASE'). If pg_dump ...
- 41
0
votes
0
answers
64
views
postgres-14, COPY partition table running even after pg_dump has completed
Running into an issue running pg_dump with -Fd -Z 9 -j 10 using postgres-14 on linux. The backup completes from the cmdline, but I see that CREATE table IF not exists statements are blocked by a pid ...
- 1
1
vote
1
answer
1k
views
PostgreSQL: restore data only if not exist
I am dumping the data of a table into a .tar file and restoring it into the same table on a different database.
C:\Program Files\PostgreSQL\12\bin\pg_dump.exe --file "C:\\SQL_ST~1\\pg_dump.tar&...
- 33
0
votes
1
answer
835
views
pg_dump on very large table fails to lock the table
I have defined a LOG table.
Then I created lot of child tables, one per minute, in the form of:
create LOG_20231209_1500 .. inherits LOG;
create LOG_20231209_1501 .. inherits LOG;
create ...
- 1,616
1
vote
1
answer
242
views
pg_dump behaviour with -B option
Recently we had to dump and restore our postgres db from one cloud provider to another one. We have used pg_dump for that and below is the command.
pg_dump -F t -h HOST -U USER -d DB -f C:\Users\...
- 1,124
2
votes
1
answer
120
views
Export domain in postgres
I want to backup some tables of an postgres db in another empty db. To do this I use pg_dump with the -t option, but when I restore it I get an error due to a missing domain.
The only solution I found ...
- 67
0
votes
0
answers
452
views
pg_dump Transaction Isolation Level Error
I'm having a hard time finding a reason/solution for this error. I'm hoping someone else has seen this and has some direction. These commands have been successful in the past from this same host. I'...
- 1
2
votes
0
answers
270
views
Enable syncing DB dump between tenants in a multi-tenant Postgres database
Our Java application stores data pertaining to multiple tenants in one single DB instance on AWS RDS. This setup is replicated across regions in prod. There are multiple schemas and each table within ...
- 801
0
votes
1
answer
992
views
pg_dump database with several schemas with PostgreSQL 12
I created a backup of a postgres database which contains the public and 3 more schemas like this:
pg_dump -a -d my_database -h localhost -U my_user -Fc > my_database.stage.2023.08.25.pgdump
When I ...
- 489