Running a Startup on AWS? Get Funding With AWS JumpStart. Click Here to Learn More

2021 Fillmore Street #1128

}

24/7 solutions

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 [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

How to Setup AWS Control Tower in Your Environment

How to Setup AWS Control Tower in Your Environment

High control and governance is a large focal point of Amazon’s Cloud services. Another solid service for maintaining the wellbeing and compliance of any AWS service is Control Tower, helping to further simplify governance with enough room to integrate third-party software for scaling. Its main function is for the construction and monitoring of new AWS environments regardless of size and complexity.

Get Marketing Help Through AWS’ Marketing Central

Get Marketing Help Through AWS’ Marketing Central

The most important step in marketing is the first step: gathering the data needed. Anything gathered during this phase will dictate everything from what is developed and how it is marketed. Having the right sponsorship and resources can significantly improve this process. Amazon’s Marketing Partner Network, a resource sponsorship program, helps to gather data on target customers and accelerate the process with additional AWS resources, tools, and ML training.

Developing E-Commerce with Amazon Web Services

Developing E-Commerce with Amazon Web Services

Amazon continues to innovate with internet retail and how the customer’s experience is enhanced digitally. AWS continues to lead in fostering innovation and support of enterprises and retailers through the use of microservices, an API-first mentality, and cloud-native infrastructure. This has helped lay the groundwork for more sustainable online storefronts and provided customers with better services.

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.