CODEWITHSUNDEEP
postgresqlpg-restore
explorer da
explorer da

Reputation: 99

pg_restore completes without any errors but data not loaded --Data Only --Dump file

In source the table1 has 4k records

Exported data using pgadmin:

C:\Users\~\AppData\Local\Programs\pgAdmin 4\v7\runtime\pg_dump.exe --file "C:\\Users\\~\\Documents\\data_20240619_1205.dump" --host "xx.xxx.xx.xxx" --port "6524" --username "devadmin" --no-password --format=c --blobs --data-only --verbose --table "myschema.table1" --table "myschema.table2" --table "myschema.table3" "source_databse_name"

200mb dump file got generated data_20240619_1205.dump

Imported using the below command (from my laptop) in command prompt:

export PGPASSWORD='my_password'

C:\Users\~\AppData\Local\Programs\pgAdmin 4\v7\runtime> pg_restore -U devadmin -h "xx.xxx.xx.xxx" -p 6524 -d target_database_name --data-only -v -n myschema --data-only --verbose --table "myschema.table1" --table "myschema.table2" --table "myschema.table3" -Fc "C:\\Users\\~\\Documents\\data_20240619_1205.dump"

pg_restore: allocating AH for C:\\Users\\~\\Documents\\data_20240619_1205.dump, format 1
pg_restore: read TOC entry 0 (ID 7032) for ENCODING ENCODING
pg_restore: read TOC entry 1 (ID 7033) for STDSTRINGS STDSTRINGS
pg_restore: read TOC entry 2 (ID 7034) for SEARCHPATH SEARCHPATH
pg_restore: read TOC entry 3 (ID 7035) for DATABASE **source_database_name**
pg_restore: read TOC entry 4 (ID 6979) for TABLE DATA table1
pg_restore: read TOC entry 5 (ID 6982) for TABLE DATA table2
pg_restore: read TOC entry 5 (ID 6982) for TABLE DATA table3
pg_restore: connecting to database for restore

In target still the table 1 has 0 records

Note: Both source and target databases are in the same hostname Both source and target databases uses the same userid to execute the export and import Not sure why in the pg_restore source_databse_name is mentioned --Mentioned in bold

Upvotes: 0

Views: 93

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247515

During pg_restore, you need to omit the schema name. So instead of --table "myschema.table2, you have to use --table "table2".

See the note in the documentation:

This flag [-t] does not behave identically to the -t flag of pg_dump. There is not currently any provision for wild-card matching in pg_restore, nor can you include a schema name within its -t. And, while pg_dump's -t flag will also dump subsidiary objects (such as indexes) of the selected table(s), pg_restore's -t flag does not include such subsidiary objects.

Upvotes: 3

Related Questions