PostgreSQL Backups and Migration: Difference between revisions
Jump to navigation
Jump to search
No edit summary |
No edit summary |
||
| Line 4: | Line 4: | ||
=== Local PostgreSQL database === | === Local PostgreSQL database === | ||
<syntaxhighlight lang=" | <syntaxhighlight lang="bash"> | ||
$ pg_dump -h hostname - d dbname -U username > mysnapshot.sql | $ pg_dump -h hostname - d dbname -U username > mysnapshot.sql | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| Line 14: | Line 14: | ||
It's also possible to compress the dump: | It's also possible to compress the dump: | ||
<syntaxhighlight lang=" | <syntaxhighlight lang="bash"> | ||
$ pg_dump -h hostname - d dbname -U username | gzip > mysnapshot.gz | $ pg_dump -h hostname - d dbname -U username | gzip > mysnapshot.gz | ||
$ gunzip -c mysnapshot.gz | psql dbname | $ gunzip -c mysnapshot.gz | psql dbname | ||
| Line 34: | Line 34: | ||
== Restoring a dump == | == Restoring a dump == | ||
<syntaxhighlight lang=" | <syntaxhighlight lang="bash"> | ||
$ psql dbname < mysnapshot.sql | $ psql dbname < mysnapshot.sql | ||
</syntaxhighlight> | </syntaxhighlight> | ||
Latest revision as of 01:53, 13 March 2020
Database snapshot[edit]
Local PostgreSQL database[edit]
$ pg_dump -h hostname - d dbname -U username > mysnapshot.sql
The password can be included in the command above, otherwise you'll be prompted for the password. [1]
The -a option will dump only the data in the database, not the structure. [2]
It's also possible to compress the dump:
$ pg_dump -h hostname - d dbname -U username | gzip > mysnapshot.gz $ gunzip -c mysnapshot.gz | psql dbname
AWS RDS PostgreSQL[edit]
- Change your database RDS instance security group to allow your machine to access it.
- Add your ip to the security group to access the instance via Postgres.
- AWS Management Console > Services > Database > RDS > Database Instances > Click on an instance name to view its details
- Connectivity & Security tab > Security > VPC security groups > Click on group name
- Inbound Rules tab > Edit inbound rules > Add IP of local machine
- In a local terminal, make a copy of the database using
pg_dump.$ pg_dump -h <public dns> -U <my username> -f <name of dump file .sql> <name of my database>- Public DNS is available via RDS Management Console > Database Instances > Click on an instance name to view its details
- Connectivity & security > Endpoint & port > Endpoint > The public DNS.
- The port number (default port for PostgreSQL is 5432) is listed below. [3]
Restoring a dump[edit]
$ psql dbname < mysnapshot.sql