Questions tagged [pg-dump]
The pg-dump tag has no usage guidance.
170
questions
0
votes
1answer
17 views
How to get lsn(xid) numbers of transactions from WAL file?
I'm looking for a way of getting transactions from WAL files.
Basically I need their LSN numbers.
(ideally both transactions LSN numbers and WAL files first and last LSN numbers)
I have a PostgreSQL ...
0
votes
1answer
33 views
PostgreSQL pg_dump -E encoding option not working
I have a UTF8 database qdb and I want to back it up to a plain file using the same UTF8 encoding. I am using pg_dump as I don't have pgAdmin working now. I however can't get pg_dump to output a UTF8-...
0
votes
1answer
51 views
pg_restore showing errors when specifying schema when backing up with pg_dump
I have created two different dump files one without specifying the schema, another specifying the public schema
without specifying the public schema'
pg_dump -h IP_ADDRESS -p 5432 -U my_user -Fc my_db ...
0
votes
1answer
25 views
Error using pg_restore
I am using the following guide to migrate a database from my Windows 10 machine to my Managed Database on Digital Ocean.
https://www.digitalocean.com/docs/databases/postgresql/how-to/import-databases/
...
2
votes
1answer
33 views
Is there a way to convert a custom format Pg dump file to a directory format?
We create a "custom" format (-Fc) dump file of our production DB each night. One of the things this is used for is restoring into a test DB. We then run a number of update queries to ...
0
votes
2answers
42 views
Where I run mysqldump or pg_dump, Both services (mysql or postgres) freeze?
I need to create a database without stopping the services and I don't know this answer.
I need my web pages to continue working properly in that time.
Can someone answer me?
0
votes
1answer
98 views
Schema pg_dump failed due to a Lock on a table
I'm running backup restore on a schema every day and get this every now and then:
pg_dump: Error message from server: ERROR: relation not found (OID
86157003) DETAIL: This can be validly caused by ...
2
votes
1answer
24 views
Does dumping/restoring do the same as “vacuum full”?
I have a large database running with PostgreSQL v11, and the disk space is very limited, so I have to VACUUM (FULL) the database frequently (e.g. every day).
But vacuuming is a very time-consuming ...
0
votes
0answers
36 views
PostgreSQL - Details/counts in pg_dump file
I'm involved with converting a system from Oracle to PostgreSQL.
We currently use database dumps quite heavily to move data between systems.
For Postgresql, I'm using pdump -Fc and restoring using ...
0
votes
0answers
107 views
How to take backup of PostgreSQL 9.4 database to remote server using pg_basebackup?
We want to take the logical and base backup on different server machine in the network. Currently both backups are running on same server machine causing space issues. We have the scripts in place ...
0
votes
1answer
56 views
Differences between .out and .sql in pg_dump PostgreSQL
I create a backup file of postgeSQL database:
>pg_dump --dbname=db_test --host=localhost --username=user -F c -f dump_test.out
In documentation for pg_dump is used extension .sql and for ...
0
votes
1answer
40 views
postgres database with multiple database object owners and pg_dump
with a postgres 11.2 database with multiple database object owners and trying to use the following command as the database owner (not the postgres superuser):
pg_dump -h hostname -p 5432 -U ...
0
votes
0answers
26 views
How Can I Move Column Definitions From One Table to Another (psql PostgreSQL)?
I have added many new columns to a table in my database in my local environment with ADD COLUMN statements, over 30. My application is split into multiple environments (UAT, STAG, PROD) and we are ...
0
votes
2answers
32 views
How should I be backing up Postgres for this scenario?
So the way I have been backing up Postgres is with:
pg_dumpall -U eox-dev -h localhost --clean --file=/home/backups/postgres.sql
I want to make sure I have a production ready backup that has ...
1
vote
1answer
121 views
Database Size is Different after pg_restore (389 GB vs 229 GB)
I created a backup of my database in a PostgreSQL 11.6 (with TimescaleDB 1.60 extension) using pg_dump:
PGPASSWORD=mypassword pg_dump -h 127.22.0.4 -p 5432 -U postgres -Z0 -Fc database_development
...
6
votes
1answer
5k views
How to Install postgresql-client-11 on Ubuntu 18.04
I need to use the pg_basebackup/pg_dump program on an Ubuntu 18.04 system to connect to a remote PostgreSQL 11.6 server.
However, the current system only contains pg_basebackup 10.10 and the remote ...
0
votes
2answers
3k views
pg_restore: error: could not read from input file: end of file
I do try to create a dump of a table, that is stored inside of my database.
The table is called
webapp_product
In order to create the dump, I do use following commands:
docker-compose exec db1 ...
0
votes
1answer
335 views
Are there limitations on using pg_dump on a materialized view such that the data won't be included in the results?
I have a database, call it maps, which contains 2 schemas, a & b. In each schema I have a number of tables: a.t1, a.t2, b.t1, b.t2 (plus others). The column sets on each of these tables is ...
0
votes
2answers
1k views
How do I dump a Postgres table and restore under another user/role?
I am getting an error when I try to restore a Postgres database table to a different user/role than from which it was dumped.
I am doing the pg_dump as OS user: 'postgres', and doing the restore as ...
0
votes
1answer
68 views
pg_dump - table dependencies when using --table
The documentation says this when using -t table:
Note When -t is specified, pg_dump makes no attempt to dump any other
database objects that the selected table(s) might depend upon.
Therefore, ...
0
votes
0answers
30 views
multiple pg_dump
My Postgres 9.3 cluster has 9 databases. I want to take backup of these databases using pg_dump utility of PostgreSQL 10.
Since individual dbs are too large in size (~350GB), I want to run pg_dump ...
0
votes
2answers
435 views
Missing data for one schema in pg_dump
I have a Postgres 9.6 database with several schemas, and a regular backup of this database is done with a command like
pg_dump -h localhost -d db -U postgres -Fc -f file.dump
When I restored a ...
0
votes
1answer
27 views
Using pg_dump to create 144 backups per day
Imagine the following scenario (this may not be a viable or sensible idea but please imagine that it is).
Using Ubuntu, a cron job calls pg_dump and backs up a Postgres database every 10 minutes. In ...
0
votes
2answers
63 views
How to use pg_restore in database with different postgis installation (postgis schema location)?
I have database1 where postgis is installed in the public schema.
And database2 where postgis is located in a schema called postgis.
When I dump database1.schema1, schema1.table1 references its geom ...
1
vote
2answers
1k views
postgresql pg_dump out of memory
is it possible to limit somehow usage of resources for pg_dump?
The problem is, whole DBase is 775GB and i have two very large tables:
pg_largeobject table is – 390GB
pg_largeobject statistics:
...
0
votes
1answer
103 views
How to dump only create table statements (or table definitions only) with pg_dump in Postgresql
I want only the table definitions, not wiews nor functions.
I have a schema named "cvp". I try:
pg_dump --schema-only -n cvp -t 'cvp.*' --format plain
And I get:
pg_dump: No matching tables ...
1
vote
1answer
360 views
pg_restore: [archiver] did not find magic string in file header: please check the source URL and ensure it is publicly accessible
I have been trying to push a dump file from my local Postgrel DB (which I uploaded into my Google Drive and is accessible to public) into my Heroku remote DB with the following URL:
heroku pg:backups:...
1
vote
1answer
562 views
Postgresql cannot restore partial pg_dump “relation ”public.my_table_seq“ does not exist”
In my database I dumped some tables via the following psql command:
pg_dump -t my_table1 -t mytable2 > my_dump.sql
And I try to import it into a postgresql docker image as a standalone database ...
0
votes
0answers
227 views
pg_dump not recognized, even though I've confirmed the binary is present
I'm trying to do a dump of a table in PostgreSQL via pgAdmin3. When I try to run the the following command:
pg_dump -t schema.table_name database_name > chosen_save_name.sql
it errors out and ...
-1
votes
1answer
2k views
pg_restore error while initializing. (Closed)
I am getting the error of:
pg_restore: [archiver (db)] Error while INITIALIZING:
pg_restore: [archiver (db)] could not execute query: ERROR: unrecognized configuration parameter "...
0
votes
0answers
233 views
Can I get pg_dump to simplify the ACL commands?
When using pg_dump with a database with non-default schema foo like so:
pg_dump --schema-only --schema='foo' -U myuser -d mydb -h myhost -W > mydb.sql
The SQL dump file is full of these seemingly ...
2
votes
1answer
65 views
pg_restore “CREATE CAST” not executed if using public or pg_catalog schemas
While moving database to another instance I'm using pg_dump and pg_restore with intermediate file.
Dump file was created like this:
pg_dump --host $SRC_DB_HOST --port 5432 "dbname=$SRC_DB user=$...
1
vote
0answers
116 views
pg_dump does not return schema for repmgr objects
While trying to browse repmgr.monitoring_history, I noticed it has many tens of millions of records. Wanting to get a peak at the indexes that (may or may not) already exist on the table, I try a ...
0
votes
1answer
594 views
Moving a large table from Localhost to remote server
Postgres 11, Windows 10, pgAdmin 4
I have a large post-gis enabled table (29million rows, 20GB + a 5GB spatial index) stored on my local machine (localhost).
I want to move this table to an AWS ...
0
votes
1answer
91 views
PSQL incomplete backup : how to debug
I've been doing backups of my database " screen ".
after connecting to PSQL and typing \l+
I'm getting (among other things):
Name | Owner | Encoding | Collate | Ctype | ...
0
votes
0answers
708 views
pg_dump can't write to drive C | Windows 10
Problem is just as the title says.
pg_dump literally can't write to anywhere on drive C: - the drive windows is installed on.
More details:
created Temp folder on C: drive
modified access - added ...
1
vote
0answers
93 views
Dumping Postgres schema from Google cloud SQL
Now this may look trivial but it is a managed service so I don't have the superuser rights. And various schema/tables/sequences have different roles/users as their owners so I can't just use pg_dump ...
0
votes
0answers
144 views
DROP DEFAULT on psql restore fails because column not existing anymore
A dump creates a command to remove default values like:
ALTER TABLE IF EXISTS test.simulation ALTER COLUMN gid DROP DEFAULT;
When on restore the column with the default value do not exist anymore, ...
0
votes
1answer
3k views
PostgreSQL pg_dump custom file input not dumping data
When I'm using custom format (-Fc) in pg_dump, the file is smaller (42kb), and when I restore it, it only restores the tables and columns, not the data.
But when I try to use plain text (-Fp), the ...
0
votes
1answer
103 views
Veryfing pg_restore database with pg_dump database
I just did a migration from Postgres 9.4 to 10 database.
How to verify if everything is okay?
I tried to look into huge tables, the rows are different (somehow in Postgres 10 then are more rows than ...
0
votes
2answers
649 views
Postgres Backup fails with: pg_dump: could not fsync file: Bad file descriptor
I am running Postgres 11 on Windows 10 and this should be my last backup before moving to Linux:
"C:\PostgreSQL\pg11\bin\pg_dump.exe" -U postgres -h localhost -p 5432 --verbose --jobs=4 --format=d --...
0
votes
1answer
848 views
Migrate complete postgres database cluster to another machine using pg_dump
I am referring to
How to get a working and complete PostgreSQL DB backup and test
In this example/solution, which part of the restore process does actually the "CREATE DATABASE sandbox"? I have been ...
3
votes
1answer
478 views
pg_dump custom archive and create backup date
I have archive file with custom format created by pg_dump.
Does it contain the backup creation date?
I can't create backups by myself. I receive the archived files over specific network channel. ...
0
votes
2answers
149 views
Does pg_dump flag -j give performance improvement up to -j 16 or -j 32 or even -j 64?
I am backing up and restoring a large DB (7+TB) ... I am using
pg_dump -j 8 -Fd
(8 parallel dump threads with directory formatting).
I just wondered if anybody has tested with a higher -j value ...
0
votes
2answers
579 views
pg_dump: Write failed: Connection reset by peer
we are trying to move all database data to other location.
we chose to perform pg_dump and we encountered few problems:
first was out of memory problem:
during pg_dump initialization we get an error:
...
2
votes
1answer
618 views
how to automatically create the associated schema when I `pg_restore` a table?
I backed a table into a file using something like pg_dump --table=ex.tab.
But when I restore the dump file into a newly created database, it complains that the schema ex does not exist. Looking into ...
0
votes
1answer
249 views
Need a “pg_dump” command to take the backup of the child tables along with its master table for PostgreSQL database
We have one master table and multiple child tables (dependent tables) associated with it.
While taking the backup of the master table , I would like to take the backup of all its child (dependent) ...
0
votes
1answer
743 views
How to know or recover my pg_dump password? [closed]
I would like to backup my Postgre db using pg_dump. However, the first question asked by the prompt is the password.
But I have no idea what it is. Indeed, it is different from my admin account ...
0
votes
1answer
84 views
Retrieving database dump after copying from remote server
I used command
pg_dump -C -h remotehost -U remoteuser db_name | psql localhost -U localuser
to copy database on remote server to my local machine.
Can I somehow retreive the dump obtained after ...
1
vote
2answers
841 views
How to dump multiple databases of the same cluster in single execution?
I have 100 databases in my cluster. I need to backup 30 databases (as single file or multiple file, anything is fine). I could do it for tables using pg_dump with -t.
Is there any way to do ...
