How to set up PostgreSQL on Compute Engine
If you don't want to install and manage your own PostgreSQL database, Google Cloud SQL provides managed PostgreSQL.
You can also use Cloud Launcher to set up PostgreSQL on Compute Engine with just a few clicks.
- Install PostgreSQL on a Compute Engine instance.
- Configure PostgreSQL for remote access.
- Configure a Cloud Platform firewall to open a port.
- Connect to PostgreSQL from a remote computer.
Before you begin
You'll need a GCP project. You can use an existing project or click the button to create a new project:
This tutorial uses billable components of GCP, including Compute Engine.
Creating a Compute Engine instance
For the purposes of this tutorial, the default machine type works fine, so you don't need to change the default setting. In production, you need to decide how much computing power is required for your application. In general, database systems tend to be more constrained by I/O bottlenecks and hard disk speed than by CPU capabilities.
Most Linux distributions have some version of PostgreSQL integrated with their package managers. For this tutorial, you use Ubuntu Trusty because it includes PostgreSQL 9.3, which has some helpful tools that aren't available in earlier versions.
- In the GCP Console, go to the VM Instances page.
- Click the Create instance button.
- Set Name to
- In the Boot disk section, click Change to begin configuring your boot disk.
- In the Preconfigured image tab, choose Ubuntu 14.04 LTS.
- In the Boot disk type section, select Standard persistent disk.
- Click Select.
- In the Firewall section, select Allow HTTP traffic and Allow HTTPS traffic.
- Click the Create button to create the instance.
It will take a few moments to create your new instance.
Stay on the VM instances page for the next step.
Setting up PostgreSQL
To set up PostgreSQL, you must install it and set up a user.
Follow these steps to install PostgreSQL on your Compute Engine instance.
- In the list of virtual machine instances, click the SSH button in the row of the instance to which you want to connect.
Update the packages. In the SSH terminal, enter the following command:
sudo apt-get update
Install PostgreSQL, including the PSQL client and server instrumentation:
sudo apt-get -y install postgresql postgresql-client postgresql-contrib
Use PSQL to complete the setup
PostgreSQL created a default user, named "postgres", during installation. This user doesn't yet have a password, so you'll need to set one.
In your SSH terminal, run the root shell:
Run PSQL as user
postgres, instead of
root, accessing the database named
sudo -u postgres psql postgres
You should see the PSQL command prompt, which looks like this:
Enter the following command to set the password:
When prompted, enter and confirm the password you've chosen.
Important: For any system with an Internet connection, use a strong password to help keep the system secure.
adminpackextension to enable the server instrumentation that you installed earlier. The console prints
CREATE EXTENSIONwhen successful.
CREATE EXTENSION adminpack;
\qto exit PSQL.
exitto exit the root shell.
To connect to your Postgres database, you'll need to change a configuration file and open a port in the firewall on Cloud Platform.
Configure PostgreSQL remote access
By default, Postgres doesn't allow remote connections. To change this setting,
you can change the file named
Follow these steps.
In the SSH terminal window, edit
pg_hba.conf. This tutorial uses the
nanoeditor, but you can substitute your favorite editor. For PostgreSQL version 9.3, you can enter:
sudo nano ../../etc/postgresql/9.3/main/pg_hba.conf
Navigate to ip4.me to get the IPv4 address of your local computer.
You'll need this IP address in an upcoming steps.
Scroll down to the bottom of the file and add the following lines:
# IPv4 remote connections for the tutorial: host all all [YOUR_IPV4_ADDRESS]/32 md5
[YOUR_IPV4_ADDRESS]with the address of your local computer. Note that the CIDR suffix
/32is used for a single address, which is what you're providing in this tutorial.
Save the file and exit the editor. In
Control+xand then use the
Returnkey to accept the prompts to save the file. Note that
nanomight not clear the console screen properly, so if you have trouble reading the text in the console after closing
clearto clear the screen.
postgresql.conffor editing. For example, enter the following command:
sudo nano ../../etc/postgresql/9.3/main/postgresql.conf
Scroll down to the line that begins with
#listen_addresses = 'localhost'.
#character to uncomment the line.
listen_addresses = '*'
'*'setting enables Postgres to listen on all IP addresses. This is a commonly used setting. When you set the IP address in
hba.confin the previous step, you restricted access to the database to only your computer.
Save the file and exit the editor.
Restart the database service. In the SSH terminal, enter:
sudo service postgresql restart
Open the network port
PostgreSQL accepts remote connections on port 5432. Follow these steps to add a firewall rule that enables traffic on this port.
In the Cloud Console, navigate to the Create a firewall rule page.
In the Network field, leave the network as default.
In the Name field, enter:
In the Direction of traffic field, create a rule as Ingress and another rule as Egress.
In Source IP Ranges, enter the same IP address you used in
hba.conf. This is the IP address of your local computer. Remember to include the
/32suffix, for example:
In Allowed protocols and ports, enter:
Note that firewall rules are a global resource, so you'll only need to create this rule once for all instances.
Connect using pgAdmin
Now you can connect to your PostgreSQL database from your computer. This tutorial uses pgAdmin, which is a popular client application for working with Postgres databases. Follow these steps.
Install pgAdmin on your local computer.
Add the server. In pgAdmin3, you can click the first icon on the left side of the toolbar. Alternatively, click File > Add server.
In the New Server Registration window, in the Name field, enter:
Navigate to the VM instances page and find the external IP address of your Compute Engine instance in the External IP column.
In the Host field, enter the external IP address of your Compute Engine instance.
In the Port field, enter:
In the Password field, enter the password that you set previously for the user named
Click OK to close the window.
You should now be connected to your PostgreSQL database that is hosted on your Compute Engine instance. You can use pgAdmin to browse and modify the database and other settings. PgAdmin also includes a PSQL console that you can use to administer the database remotely.
This tutorial provided you with a basic look at a one-machine, single-disk installation of PostgreSQL. In a production environment, it's a good idea to employ strategies for high availability, scalability, archiving, backup, load balancing, and disaster recovery. For information about disaster recovery planning, see How to Design a Disaster Recovery Plan.
For better performance and data safety, install the database engine on the boot disk as this tutorial showed, and then set up the data storage on a separate persistent disk. To learn how to add a disk for your database, see the follow-up tutorial How to Set Up a New Persistent Disk for PostgreSQL Data.
For machines that have an Internet connection, use only strong passwords and limit access only to trusted IP ranges.
After you've finished the PostgreSQL tutorial, you can clean up the resources you created on Google Cloud Platform so you won't be billed for them in the future. The following sections describe how to delete or turn off these resources.
Deleting the project
The easiest way to eliminate billing is to delete the project you created for the tutorial.
To delete the project:
- In the Cloud Platform Console, go to the Projects page.
- Click the trash can icon to the right of the project name.
Warning: Deleting a project has the following consequences:
If you used an existing project, you'll also delete any other work you've done in the project. You can't reuse the project ID of a deleted project. If you created a custom project ID that you plan to use in the future, you should delete the resources inside the project instead. This ensures that URLs that use the project ID, such as an appspot.com URL, remain available.
To delete a Compute Engine instance:
- In the Cloud Platform Console, go to the VM Instances page.
- Click the checkbox next to your postgres-tutorial instance.
- Click the Delete button at the top of the page to delete the instance.
Deleting firewall rules for the default network
To delete a firewall rule:
- In the Cloud Platform Console, go to the Firewall Rules page.
- Click the checkbox next to the firewall rule you want to delete.
- Click the Delete button at the top of the page to delete the firewall rule.