a

Migrate PostgreSQL AWS Database Instances Quickly

Learn how to migrate PostgreSQL AWS database instances like an expert. We had the need to migrate a Postgres AWS database from a Production to Staging EC2 yesterday, so we decided to write it up for posterity. Navigate out to the production instance via ssh. I will typically write a shell script that executes the following […]

Learn how to migrate PostgreSQL AWS database instances like an expert.
We had the need to migrate a Postgres AWS database from a Production to Staging EC2 yesterday, so we decided to write it up for posterity.
Navigate out to the production instance via ssh. I will typically write a shell script that executes the following command. You will need to make sure that pem file has the proper permissions.

ssh -i amazon-production.pem [email protected]

Once you’re out on the production box, we’re going to make use of the pg_dump command. There are a number of ways to execute the pg_dump command. I’ve had the best success using the following command where the postgres database is running under the user postgres. After the pg_dump command is the name of the database. We’re going to pipe the entire database contents out to a file in the/home/deploy directory, which will have all of the contents of the db.

sudo -u postgres pg_dump database_name > /home/deploy/database_name.sql

Open the database_name.sql file in vi, you’ll see raw ddl.
Next, we need to get the database_name.sql off of the production instance. For this example, we’re just going to scp the file down to my box. The following scp command would be executed from my laptop. This command will bring the database_name.sql down to my machine.

scp -i amazon-production.pem dep[email protected]:/home/deploy/database_name.sql database_name.sql

After we have the database_name.sql on my local drive, the next step is to upload the file to the staging server. We again use a scp command to place the database_name.sql up on staging.

scp -i amazon-staging.pem database_name.sql [email protected]:/home/ubuntu/database_name.sql

Next, we ssh into the staging server

ssh -i amazon-staging.pem [email protected]

Once, we’re in on the staging server, we need to get into the postgres user and create a new database, we can do that with the following commands:

sudo su postgres

psql

create database database_name template0;

Next, we’ll import the database_name.sql into the freshly created database_name

psql database_name < /home/ubuntu/database_name.sql

When you navigate into the new database with psql, you should be able to select the database and see the tables

\c database_name

\dt

In certain situations, you may need to rename a database name or two, in these situations, I’d use the following sql command from psql

alter database database_name rename to new_database_name

In certain situations, the database may have open connections, which prevent you from renaming. In these situations, you may want to kill the existing sessions with the following command:

SELECT pg_terminate_backend( pid ) FROM pg_stat_activity WHERE pid <> pg_backend_pid( ) AND datname = ‘database_name’;

Now you can migrate Postgres AWS databases like a champ.


Joel Garcia

Joel Garcia

Joel Garcia has been building AllCode since 2015. He’s an innovative, hands-on executive with a proven record of designing, developing, and operating Software-as-a-Service (SaaS), mobile, and desktop solutions. Joel has expertise in HealthTech, VoIP, and cloud-based solutions. Joel has experience scaling multiple start-ups for successful exits to IMS Health and Golden Gate Capital, as well as working at mature, industry-leading software companies. He’s held executive engineering positions in San Francisco at TidalWave, LittleCast, Self Health Network, LiveVox acquired by Golden Gate Capital, and Med-Vantage acquired by IMS Health.

Related Articles

Navigating AWS Complexity

Navigating AWS Complexity

Amazon’s Web Services is a very complex platform. Streamlining and optimizing production workflows can be challenging for inexperienced users. However, the benefit of learning grants options for better efficiency, reliability, security, and cost-effectiveness for operations run on AWS.

While complexity can be difficult to navigate, it’s not impossible. With the right level of expertise, AWS complexity can be navigated with ease.

What is Amazon Managed Grafana?

What is Amazon Managed Grafana?

Grafana stands out as a widely embraced open-source analytics and visualization platform, celebrated for its versatility in handling diverse data sources and delivering compelling dashboards and graphs. Renowned for its user-friendly interface, Grafana simplifies the process of data interpretation and enhances the overall experience by providing interactive visualizations.

AWS and re:Invent 2023

AWS and re:Invent 2023

There are plenty of AWS enthusiasts around the world such as ourselves with ideas on how to apply the Cloud in new and innovative ways. It’s a keynote where these enthusiasts come together, network, and share innovations and new methodologies with the public. Even for people less familiar with AWS, it is a great place to get first-hand experience with the platform either unguided or with professional help to see what opportunities the platform has.

Download our 10-Step Cloud Migration ChecklistYou'll get direct access to our full-length guide on Google Docs. From here, you will be able to make a copy, download the content, and share it with your team.