Postgresql – downloading a big database (postgres) to a local copy

databasedatabase-backuppostgresqlrds

I have a pretty big postgres DB on Amazon RDS (about 9GB when zipped), and sometimes we need to copy it and do some tests on it on our local machines.

Doing a DB dump (pg_dump) and downloading it is simply too slow and honestly been just getting stuck last few times we tried.

Is there a simple way to get parts of the DB in a smart way? for example, get only changes from last 10 days, and then we can merge them with the local DB we have, or maybe getting DB in chunks etc?

I'm sure I'm not the first one with that need, but couldn't find a decent method or tutorial to explain the best ways to do it.

Thanks!

Best Answer

9GB compressed dump isn't really that large. You just need to make do it right:

dump from EC2 instance in the same AWS account and region that RDS database is running — not over the internet;
use directory dump format (--format=directory or -Fd) — it's automatically compressed;
use parallel dump (for example --jobs=16 or -j16) and parallel restore;
use sslmode=disable in connection string or env PGSSLMODE=disable pg_dump … to disable SSL — some versions of AWS RDS have 64GB limit of SSL data over single connection;
remember that RDS database restored from snapshot is terribly slow until all the data is read at least once - first read is over 10 times slower than normal, as the data is pulled from archive storage (S3 maybe) on demand — don't do this dump from a db restored recently from snapshot;
move it out of AWS only after successfull dump in compressed state.

Related Solutions

PostgreSQL – Default Superuser Username/Password After New Install

CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!

This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.

What you should do is follow Chris James's answer:

sudo -u postgres psql postgres

# \password postgres

Enter new password:

To explain it a little bit. There are usually two default ways to login to PostgreSQL server:

By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql. Note that sudo -u does NOT unlock the UNIX user.
by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).

So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.

Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.

Postgresql – Giving a database an alias in postgres

You cannot create an alias to a schema or database since the Schemaname / DBname is used internally as primary key for the tables pg_namespace and pg_database.

Best Answer

Related Solutions

PostgreSQL – Default Superuser Username/Password After New Install

Postgresql – Giving a database an alias in postgres

Related Topic