683 questions
-2
votes
0
answers
20
views
PGDump and PGRestore on/from Production PG DB [closed]
I wanted to take backup of production postgres server and restore it for data refresh on lower environments.
So we are thinking to use pg_dump and pg_restore utility for same, but while testing this ...
0
votes
0
answers
55
views
PostgreSQL pg_dump Fails: Password Authentication Error with Special Characters in Password
I am trying to execute a pg_dump command from PostgreSQL using a stored procedure. My setup uses EXECUTE format to dynamically construct the command. Here is the relevant code:
'COPY (SELECT 1) TO ...
- 131
0
votes
1
answer
63
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,327
8
votes
1
answer
5k
views
Why does pg_dump include "transaction_timeout" if psql doesn't understand it? [closed]
I'm running PostgreSQL 15.7 (in Docker) and connecting with psql and pg_dump 17.0 on Ubuntu 22.04.
I have two databases:
db1 contains my app schema
db2 is empty (newly created)
I run pg_dump to dump ...
- 8,004
0
votes
0
answers
45
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
37
views
Postgresql dump unusable due to late creation of a collation
There seems to be a problem with the order of creation and using a collation in a Postgresql dump. I defined a collation for my database a long time ago. This is used for exactly one column in a table....
- 1,432
0
votes
0
answers
91
views
pg_dump & pg_restore files on windows has 0 bytes size
I had installed DBeaver CE 24.1.1.202406231636 on a laptop running windows 11 64 bits.
I am using to manage a little database running in PostgreeSQL 16.3 in Ubuntu 22.04
I have been trying to make a ...
0
votes
0
answers
64
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
13
views
piping from borg into docker container
Hej,
i am having a hard time to get this running. Read all the other answers but some how my case does not fit.
I am doing a pg_dump backup from a container into a borg repo:
docker exec container ...
- 1
0
votes
0
answers
99
views
Opened question about pg_dump/pg_restore behaviour when (not) specifying database schema
recently, I had to dump a postgresql database by doing:
pg_dump --format=custom --compress=6 --blobs --database mydb --schema public ... -f dump.bin
this generates a binary dump file, that I convert,...
0
votes
1
answer
41
views
pg_dump Issue While Exporting Schema of Existing Postgres Database for YugaByte Migration
I have an existing postgres database which I want to migrate to YugaByte using yb-voyager. I used the below command to export the postgres database schema.
yb-voyager export schema --export-dir <...
- 12k
0
votes
0
answers
260
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
0
answers
59
views
Postgres pg_dump exceed diskspace
I have been trying to run a pg_dump on a database that is showing 40gb in pg_database_size(),
I am aware that pg_database_size() is not the best correlation to the end dump file size.
I have tried to ...
- 101
0
votes
1
answer
237
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
61
views
Is it unsafe to run pg_dump on a cluster that has pg_basebackup currently running?
I have run pg_dump and pg_basebackup hundreds of times. However, maybe for the first time I ran them at the same time and it seems to have caused the server to crash! Specifically, pg_basebackup was ...
- 1,627
0
votes
0
answers
33
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 --...
- 409
0
votes
1
answer
59
views
Aurora Postgresql - data migration using pg_dump
I have 2 Aurora postgresql DB (A and B), sitting under 2 different Aurora clusters. In order to migrate data(only) from DB A to DB B, do I need to install pg_dump extension on the clusters, or just ...
- 43
0
votes
1
answer
68
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
-2
votes
1
answer
99
views
Error while trying to dump Postgres db from pod
Could you please advice how to solve the issue I faced. I am trying to do dump from Kubernetes with the command:
kubectl exec auth-module -- bash -c "pg_dump -U pg-user database-name" > ...
- 409
0
votes
2
answers
91
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
0
votes
0
answers
212
views
Azure Postgresql Felxible server: pg_dump aborting because of server version mismatch [duplicate]
I am trying to take backup of Azure Postgresql Flexible Server using pg_dump command. I have followed this article.
My database is on Postgresql v15.6, but when I connected to the Azure Postgresql ...
- 1,054
1
vote
0
answers
66
views
django-dbbackup: server and pg_dump version mismatch
I faced on this error. who can help me?
command:
python manage.py dbbackup
error:
Traceback (most recent call last):
File "/passchain/manage.py", line 22, in <module>
...
0
votes
2
answers
355
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 ...
- 409
0
votes
1
answer
34
views
Can Postgres Java client COPY query write to local hard drive?
I have two Docker containers. One is running a Spring Boot application. The other is running Postgres.
I am trying to get three tables from Postgres onto the Java server in a format that I can ...
- 990
3
votes
1
answer
268
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: -; ...
1
vote
1
answer
773
views
Unable to pg_dump from supabase project
First of all, for some reason, pg_restore works. But when I try pg_dump, I get this error:
pg_dump: error: connection to server at "aws-0-ap-southeast-1.pooler.supabase.com" (52.74.252.201),...
- 709
0
votes
0
answers
91
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
3
votes
0
answers
205
views
Can I load a pandas dataframe from a Postgres dump file?
My Postgres database is locked on a server with no external connectivity, but I can run pg_dump and download the file.
The database contains about 30 tables, but I only need three or four for the ...
- 990
0
votes
1
answer
293
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 ...
- 11
1
vote
0
answers
181
views
Postgres: pg_restore cmd not restoring the data in my database
I created a dump of my neon postgres db using this command: pg_dump -Fc -v -d <source_database_connection_string> -f <dump_file_name>. I renamed two of my misspelt columns in my prisma ...
- 245
0
votes
0
answers
49
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
0
votes
0
answers
441
views
pg_restore throws end of line error when the dump file is edited
using pg_dump to dump the database in a custom format archive
pg_dump -U $PG_USER -d $PG_DB -a --column-inserts -Z 0 -Fc > db.dump
i have to use subquery to create new temporary tables based on the ...
- 67
1
vote
1
answer
752
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
382
views
How to update selected tables in a Postgres database schema from a database dump on another server?
I have a database and some tables are updated periodically on the dev server and need to be pushed to the database on the prod server. What's the appropriate way to migrate the updated tables from ...
- 1,327
0
votes
1
answer
387
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,596
32
votes
2
answers
12k
views
Why pg_dump/psql throws "FATAL: no PostgreSQL user name specified in startup packet"?
I am trying to run pg_dump (or even psql) over our postgresql server v13, with binaries from v13 and v16. Both don't work for me, but do work for my colleagues that are not on Mac, not sure if it's ...
- 1,841
1
vote
1
answer
178
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
3
votes
1
answer
873
views
Restoring DB using psql faster than pg_restore?
I'm trying to speed up the process of dumping and restoring databases. At the moment, we do pg_dump --no-privileges --no-owner -w source_db | psql target_db (the DB is created before this). I read ...
- 197
2
votes
1
answer
93
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
1
vote
2
answers
533
views
"function public.pg_stat_statements_reset() does not exist" error on restoring dump
I am taking dump of a database using pg_dump using this command
pg_dump -U [username] -h <[hostname] -p [port] -v [database_name] > dump.sql
and restoring to a new database with
psql -U [...
- 87
0
votes
0
answers
326
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
0
votes
2
answers
321
views
Segmentation fault on downgraded pg_restore (Postgresql 10 -> Postgresql 9.5)
On a server with Postgresql 10 did a dump and put it to rsync.net
pg_dump -Z1 -Fc <db_name> | ssh <user>@s<server> "dd of=dump"
On the other server with Postgresql 9.5 ...
- 1,043
2
votes
0
answers
208
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
243
views
Except for pg_dump What else can I do to backup a postgreSQL database
Except for pg_dump What else can I do to backup a postgreSQL database.Pg_dump shoul run in postgresql server,but i only konw the database connection info.
I try to use sql to do this.But there are to ...
0
votes
1
answer
794
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 ...
- 373
0
votes
1
answer
344
views
pg_dump fails to dump specific schema
I am using pg_dump (15.3) to dump information on a database.
This command works:
pg_dump --schema-only --schema foo -d foodb -U postgres -Fp and dumps among other things a CREATE SCHEMA foo; statement
...
- 933
0
votes
1
answer
193
views
docker exec pg_dump in bash function
I'm trying to run pg_dump in a docker container, in a bash function, but I'm getting an error can't create : nonexistent directory. The dir does exist, so I suspect that the output redirection (>) ...
- 3,618
0
votes
2
answers
559
views
Postgres database roles and privileges
Database role XYZ does not have any privilege.How assign privileges for that particular role to create a backup of database using pg_dump?
Grant SELECT ON ALL TABLES IN Schema public TO :XYZ
Is it ...
- 27
-2
votes
1
answer
45
views
Move several databases from one remote database to another in PostgreSQL
I have to move several tables from one remote database to another in PostgreSQL.
From my understanding the only way of doing it is via the pg_dump.
pg_dump -h <source_db_host> -U <...
- 61
0
votes
0
answers
126
views
pg_dump stops with this message free(): double free detected in tcache 2
We need to create a logical backup of the database, and we initiate the process with the following command:
pg_dump -U postgres -Fc -Z6 -f /tmp/backup.dump DOC
After some time, we encounter the ...
