Overview
During development, data from the production and staging environments may be imported locally for defect investigation and reproduction testing.
This time, assuming such a situation, we will show an example of the procedure for exporting existing data (data under development locally) and importing survey data (production or staging data).
Export
Use pg_dump to export.
If you have PostgreSQL installed locally, it should usually be in:
C:\Program Files\PostgreSQL\12\bin\pg_dump
Command
Enter the following at the command prompt:
[pg_dump installation path] -h [host name] -p [port number] -U [user name] -d [schema name] -v > [file path to export]
*-v : (Optional) output progress to console
(Example) “C:\Program Files\PostgreSQL\12\bin\pg_dump” -h localhost -p 5432 -U postgres -d sample_schema -v > “C:\postgresql\dump\local.dump”
Delete all existing data
Delete all data in advance, as importing usually fails if existing data remains.
Command
Log in to PostgreSQL with a command prompt.
psql -h [host name] -p [port number] -U [user name] -d [schema name]
Delete all schemas.
drop schema public cascade;
create schema public;
Import
Import data for investigation.
Commnd
Enter the following at the command prompt:
psql -h [host name] -p [port number] -U [user name] -d [schema name] -f [import file path]
(例) psql -h localhost -p 5432 -U postgres -d govmates -f "C:\postgresql\dump\staging.dump"
Summary
- Use pg_dump for export.
- If there is existing data, the import may fail, so delete all of it.
- The import uses the psql command.
Refer to
(Note) The following sites are in Japanese.
Export/import DB dump in PostgreSQL
How to drop all tables in PostgreSQL
Comments