Title	Reason	Reference
Set up PostgreSQL on Google Compute Engine Virtual Machines for Remote Access	Wasted around 3 hours why my postgres installation was not accessible outside the machine, although all setup was correct. finally found it.	https://cloud.google.com/community/tutorials/setting-up-postgres

Objectives

• Install PostgreSQL on a Compute Engine instance.
• Configure PostgreSQL for remote access.
• Configure a Google Cloud firewall to open a port.
• Connect to PostgreSQL from a remote computer.

Before you begin

You'll need a Google Cloud project. You can use an existing project or create a new project.

Create 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 20.04 LTS (Focal Fossa) because it includes PostgreSQL 12.5, which has some helpful tools that aren't available in earlier versions.

1. In the Cloud Console, go to the VM instances page.
2. Click Create instance.
3. In the Name field, enter postgres-tutorial.
4. In the Boot disk section, click Change.
5. In the Boot disk window, perform the following steps in the Public images tab:
   1. In the Operating system menu, select Ubuntu.
   2. In the Version menu, select Ubuntu 20.04 LTS.
   3. In the Boot disk type menu, select Standard persistent disk.
   4. Click Select.
6. In the Firewall section, expand Management, security, disks, networking, sole tenancy, and then expand Networking.
7. In the Network tags field, enter postgres-tutorial.
8. Click Create 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.

Install PostgreSQL

Follow these steps to install PostgreSQL on your Compute Engine instance.

1. Login to the virtual machine using ssh or any other method you want.

2. Update the packages. In the SSH terminal, enter the following command:

   sudo apt update
   

3. Install PostgreSQL, including the PSQL client and server instrumentation:

   sudo apt -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 need to set one.

1. Run psql as user postgres, instead of root, accessing the database named postgres:

   sudo -u postgres psql postgres
   

   You should see the PSQL command prompt, which looks like this: postgres=#

2. Enter the following command to set the password:

   \password postgres
   

3. When prompted, enter and confirm the password you've chosen.

4. Install the adminpack extension to enable the server instrumentation that you installed earlier. The console prints CREATE EXTENSION when successful.

   CREATE EXTENSION adminpack;
   

5. Enter \q to exit PSQL.

Connecting remotely

To connect to your Postgres database, you need to change a configuration file and open a port in the firewall on Google Cloud.

Configure PostgreSQL remote access

By default, Postgres doesn't allow remote connections. To change this setting, you can change the file named pg_hba.conf.

Caution: On production systems, or any system that has an internet connection, use strong authentication methods and restrict traffic to only those users and IP addresses that you want to connect to each database.

Edit pg_hba.conf

1. In the SSH terminal window, edit pg_hba.conf. This tutorial uses the nano editor, but you can substitute your favorite editor. For PostgreSQL version 12, you can enter:

   sudo nano /etc/postgresql/12/main/pg_hba.conf
   

2. Navigate to ip4.me to get the IPv4 address of your local computer.

   You need this IP address in an upcoming steps.

3. 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
   

   Replace [YOUR_IPV4_ADDRESS] with the address of your local computer. Note that the CIDR suffix /32 is used for a single address, which is what you're providing in this tutorial.

   You can add as many as ip address you want, just add another line.

4. Save the file and exit the editor. In nano, press Control+x, press y, and then use the Enter key to accept the prompts to save the file. Note that nano might not clear the console screen properly, so if you have trouble reading the text in the console after closing nano, enter clear to clear the screen.

Edit postgresql.conf

1. In the terminal, enter the following command:

   sudo nano /etc/postgresql/12/main/postgresql.conf
   

2. Scroll down to the line that begins with #listen_addresses = 'localhost'.

3. Delete the # character to uncomment the line.

4. Replace localhost with *:

   listen_addresses = '*'
   

   The '*' setting enables Postgres to listen on all IP addresses. This is a commonly used setting. When you set the IP address in hba.conf in the previous step, you restricted access to the database to only your computer.

5. Save the file and exit the editor.

6. Restart the database service. In the 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.

1. In the Cloud Console, navigate to the Create a firewall rule page.

2. In the Name field, enter postgres-tutorial.

3. In the Network field, leave the network as default.

4. In the Direction of traffic field, select Ingress.

5. In the Action on match field, select Allow.

6. In the Targets menu, select Specified Target tags.

7. In the Targets tags field, enter the network tag (postgres-tutorial) that you used for the instance.

8. In the Source filter menu, select IPv4 ranges.

9. In the Source IPv4 ranges field, enter the same IP address that you used in hba.conf.

   This is the IP address of your local computer. Remember to include the /32 suffix, for example: 1.2.3.4/32.

10. In Specified protocols and ports, check tcp, and enter 5432 for the value.

11. Click Create.

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.

1. Install pgAdmin on your local computer.

2. (macOS only) Move pgAdmin to a location from which you can run it:

   1. Right-click the pgAdmin icon and copy it.
   2. Open the macOS Application folder, and paste pgAdmin into this folder.

3. Start pgAdmin by clicking its icon in the Application folder.

4. Add the server. In pgAdmin4, you can click the first icon on the left side of the toolbar. Alternatively, click File > Add server.

5. In the New Server Registration window, in the Name field, enter the following:

   Postgres tutorial
   

6. On the VM instances page, find the external IP address of your Compute Engine instance in the External IP column.

7. In pgAdmin, in the Connection tab, in the Hostname/address field, enter the external IP address of your Compute Engine instance.

   Note: Enter only the address as it appears in the Cloud Console; don't add any protocol specifiers, such as http:// or other characters.

8. In the Port field, enter 5432.

9. In the Password field, enter the password that you set previously for the user named postgres.

10. Click Save 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.