It’s always a good practice to keep your database upgraded to the latest stable release. It helps in leveraging new features, keeping up security patches and other bug fixes.
Whenever we had to run a major version upgrade of our PostgreSQL database cluster, we had to take a complete dump. It becomes cumbersome specially when the database size is in terabytes.
Note: PostgreSQL minor version upgrades do not require a complete dump and restore. Only major versions require it.
Durning this process we need to make sure we have enough disk space to take a complete dump. Here AWS S3 comes in handy. We can run a pg_dump and store the dump directly on S3 bucket rather than storing on disk.
Below are the following commands that will help you in taking a complete dump and restoring back your database cluster.
Running the dump command:
PGPASSWORD=$(cat .pgpass) pg_dump -Z 9 -v -h database-host -U your-root-user -d awesomeDb --no-owner --no-acl --no-privileges --no-tablespaces | aws s3 cp - s3://my-backups/projects/my-project-20102020.sql.gz > /dev/null &As you can see above this command will run in background and take a complete dump. PGPASSWORD is used here to read the database password from environment variables. You can store your PostgreSQL password inside .pgpass file. The cat command will read the password and store it in the environment variable. We are ignoring owner, acl , privileges and tablespaces as we will be creating them after restoring the database.
Restoring your PostgreSQL database:
CREATE DATABASE awesomeDbgunzip < 20102020.sql.gz | psql awesomeDb
Using gunzip we can restore our database into the new upgraded cluster.
Creating database user:
CREATE USER awesome_user WITH PASSWORD '2xYThHc5BXCDPwDJkfmMuBTTS';GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO awesome_user;GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO awesome_user;
Thats it!
You should be all set now!
