Skip to content

Instantly share code, notes, and snippets.

@kevinke

kevinke/pg_dump_restore.sql

Last active November 22, 2018 03:19
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save kevinke/8996dc420030c099f8cdfbb741345dda to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save kevinke/8996dc420030c099f8cdfbb741345dda to your computer and use it in GitHub Desktop.
Download ZIP
PG Dump and Restore
Raw
pg_dump_restore.sql
pg_dump -F c -v -O -c --if-exists -C -U 'root' -h '139.198.176.101' -p '15432' -d 'qingcloud' -f 'pr_pro_db_test.dump'
dump related param
-f [file path]: send output to the specified file
-F [format]: c is for custom format
-v: verbose
-O: (an upcase 'Oscar') do not output commands to set ownership of objects
-c: output commands to clean (drop) database objects prior to outputting the commands for creating them
--if-exists: se conditional commands (i.e. add an IF EXISTS clause) when cleaning database objects.
-C: begin the output with a command to create the database itself and reconnect to the created database.
connection related param
-h: host
-p: port
-U: user
-d: dbname
[//]: # kill active session before restore db dump
SELECT
pg_terminate_backend(pid)
FROM
pg_stat_activity
WHERE
-- don't kill my own connection!
pid <> pg_backend_pid()
-- don't kill the connections to other databases
AND datname = 'database_name';
and restore with this:
pg_restore 'pr_pro_db_test.dump' -F c -v -O -d ghl_pr_development -U 'postgres' -h localhost -p 15432
don't need -c and -C if you restore to another database
create database with owner
createdb -e -O <username> digital -h <host> -p <port> -U <root>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment