PostgreSQL Backups and Migration: Difference between revisions
Jump to navigation
Jump to search
No edit summary |
|||
| (3 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
[[Category:PostgreSQL]] [[Category:Web Development]] | [[Category:PostgreSQL]] [[Category:Web Development]] [[Category:AWS]] | ||
== database | == Database snapshot == | ||
=== Local PostgreSQL database === | |||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
| Line 6: | Line 8: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
The password can be included in the command above, otherwise you'll be prompted for the password.<ref>[http://www.postgresql.org/docs/8.3/static/backup-dump.html SQL Dump] PostgreSQL 8.3.23 documetation</ref> | The password can be included in the command above, otherwise you'll be prompted for the password. <ref>[http://www.postgresql.org/docs/8.3/static/backup-dump.html SQL Dump] - PostgreSQL 8.3.23 documetation</ref> | ||
The `-a` option will dump only the data in the database, not the structure. | The `-a` option will dump only the data in the database, not the structure. <ref>[http://www.postgresql.org/docs/8.3/static/app-pgdump.html `pg_dump`] - PostgreSQL 8.3.23 documentation</ref> | ||
It's also possible to compress the dump: | It's also possible to compress the dump: | ||
| Line 16: | Line 18: | ||
$ gunzip -c mysnapshot.gz | psql dbname | $ gunzip -c mysnapshot.gz | psql dbname | ||
</syntaxhighlight> | </syntaxhighlight> | ||
=== AWS RDS PostgreSQL === | |||
* 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. <ref>[https://gist.github.com/syafiqfaiz/5273cd41df6f08fdedeb96e12af70e3b How to copy production database on AWS RDS(postgresql) to local development database] - GitHub Gist</ref> | |||
== Restoring a dump == | == Restoring a dump == | ||
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