Deploy your Bitnami PostgreSQL Stack on Microsoft Azure Multi-Tier Solutions now! Launch Now

Bitnami PostgreSQL for Microsoft Azure Multi-Tier Solutions

Description

PostgreSQL (Postgres) is an open source object-relational database known for reliability and data integrity. ACID-compliant, it supports foreign keys, joins, views, triggers and stored procedures.

What are the differences between a Bitnami Single-Tier Solution and Multi-Tier Solution?

Single-tier architecture implies that all the required components of an application run on a single server. If your environment is growing and becoming more complex, a single layer architecture will not meet your scalability requirements. Single-Tier Solutions are great for departmental applications, smaller production environments, new users, or those applications that don't support multi-tier architectures.

The typical architecture of a Bitnami Single-Tier Solution looks like this:

Single-tier architecture

Multi-tier architecture involves more than one server and infrastructure resource. For example, the Front End-Database topology separates the application server from the database server. This allows you to extend workloads in the cloud and tailor your application to meet specific scalability and reliability goals. Multi-Tier Solutions provide more sophisticated deployment topologies for improved scalability and reliability for larger production or mission critical environments.

TIP: Not sure if you have chosen the right solution? Check out the Bitnami Multi-Tier solutions features and benefits to learn more about the benefits of Multi-Tier.

This Bitnami Multi-Tier Solution uses multiple virtual machines to replicate the databases from the master node to a configurable number of replicas. This topology is illustrated below:

Multi-tier architecture

First steps with the Bitnami PostgreSQL Stack

Welcome to your new Bitnami application running on Microsoft Azure Multi-Tier Solutions! Here are a few questions (and answers!) you might need when first starting with your application.

What credentials do I need?

You need two sets of credentials:

  • The application credentials that allow you to log in to your new Bitnami application. These credentials consist of a username and password.
  • The server credentials that allow you to log in to your Microsoft Azure Multi-Tier Solutions server using an SSH client and execute commands on the server using the command line. These credentials consist of an SSH username and key.

What is the administrator username set for me to log in to the application for the first time?

Username: postgres

What SSH username should I use for secure shell access to my application?

SSH username: bitnami

How to start or stop the services?

NOTE: The steps below require you to execute the commands on the remote server. Please check our FAQ for instructions on how to connect to your server through SSH.

Each Bitnami server includes a control script that lets you easily stop, start and restart all the services installed on the current individual server.

Obtain the status of a service with the service bitnami status command:

$ sudo service bitnami status

Use the service bitnami command to start, stop or restart all the services in a similar manner:

  • Start all the services.

    $ sudo service bitnami start
    
  • Stop all the services.

    $ sudo service bitnami stop
    
  • Restart all the services.

    $ sudo service bitnami restart
    
TIP: To start, restart or stop individually each server of the cluster, check the FAQ section about how to start or stop servers in a Multi-Tier Solution.

What is the default configuration?

The default configuration consists of:

  • A database cluster or directory under which all data will be stored. The default data directory in Bitnami is located at /opt/bitnami/postgresql/data.
  • Every connection to the PostgreSQL database server is made using the name of some particular role. A database superuser bypasses all permission checks, except the right to log in. Bitnami configures a default superuser role with a name of postgres. The postgres role has remote access to the database.

Check our recommendations for a production server.

PostgreSQL version

To obtain the PostgreSQL version, execute the following command:

$ postgres --version

PostgreSQL configuration files

The PostgreSQL configuration settings file is located at /opt/bitnami/postgresql/conf/postgresql.conf on the database server host.

The PostgreSQL client authentication configuration file is located at /opt/bitnami/postgresql/data/pg_hba.conf on the database server host.

The PostgreSQL official documentation has more details on how to configure the PostgreSQL database.

PostgreSQL port

The default port in which PostgreSQL listens is 5432.

PostgreSQL log file

The main PostgreSQL log file is created at /opt/bitnami/postgresql/logs/postgresql.log file. You can change the default error reporting and logging configuration settings in the postgresql.conf file.

NOTE: On some platforms, you may need root account privileges to view these files.

How is the cluster configured?

The Bitnami Multi-Tier Solution for PostgreSQL uses multiple VMs, consisting of 1 master and 1 or more slaves, to provide a horizontally scalable and fault-tolerant deployment. Data automatically replicates from the master node to all slave nodes. PostgreSQL server is configured to listen for connections from any IP address (0.0.0.0).

To understand how it works, consider the example below of a two-node cluster (one master and one slave):

  • On the master node, create a new table and populate it with some data:

     postgres=# \c bitnami
     You are now connected to database "bitnami" as user "postgres".
    
     bitnami=# CREATE TABLE test (id INT NOT NULL, value VARCHAR(255) NOT NULL);
     CREATE TABLE
    
     bitnami=# INSERT INTO test VALUES (1, 'foo'), (2, 'bar');
     INSERT 0 2
    
  • On any of the slave nodes, check if the table exists and list its contents. It should display the same data originally entered on the master node:

     postgres=# \c bitnami
     You are now connected to database "bitnami" as user "postgres".
    
     bitnami=# select * from test;
      id | value
     ----+-------
       1 | foo
       2 | bar
      (2 rows)
    

This shows that records added on the master node are automatically replicated to the slave node(s). For more information, refer to the PostgreSQL documentation on replication.

How to check cluster replication status?

To check the status of your PostgreSQL cluster, log in to the master or primary database server host using SSH, start the PostgreSQL command-line client using your administrative credentials, and run the following command within it:

SELECT client_addr, state FROM pg_stat_replication;

The output of this command will be a list of cluster nodes and their current replication status.

How to connect to cluster nodes?

Some operations such as changing the application password, require that some actions will be repeated in each cluster node. That way, you need to connect to each node for the changes to take effect in the whole cluster. Follow the steps below to connect the cluster nodes in your Azure deployments:

  • Log in to the Microsoft Azure portal.
  • Navigate to the "Virtual Machines" section and find your deployment.
  • Select the primary node from the virtual machines list. It usually finishes with the number 0:

    Select the primary node

  • In the resulting screen, click "Connect". It displays the command to connect through SSH to the selected node:

    Connect through SSH to the primary node

  • Open a new terminal window on your local system and paste the command shown above. You will be prompted to enter your password. After this, you should be connected to the primary node as shown below:

    Connect through SSH to the primary node

Once you have connected to the primary node, you are able to connect to the rest of the nodes establishing an SSH connection to each node IP address as follows:

  • To find the private IP address of a node, select it in the list of virtual machines and click "network/default-subnet".

    Find node private IP address

  • Copy the IP address of the node you want to connect.

    Copy node IP address

  • In the terminal window, execute the following command (within the primary node). Remember to replace the NODE_IP_ADDRESS placeholder with the correct value:

    $ ssh bitnami@NODE_IP_ADDRESS
    

    Connect to a secondary node

    NOTE: Remember to repeat the same operation to connect to each cluster node.

How to add nodes to the cluster?

IMPORTANT: These steps assume that you have already installed the Microsoft Azure command-line client (Microsoft Azure CLI) on your system and you are signed in to Microsoft Azure through it. If this is not the case, please refer to the FAQ for instructions on how to install and sign in to Microsoft Azure using the Azure CLI.
NOTE: To follow the steps below, you will need the subscription ID, deployment ID and resource group ID for the deployment to which you wish to add nodes. Find out how to obtain the subscription ID and the deployment and resource group IDs.

To add nodes to the cluster, follow these steps:

  • Set the subscription ID for your deployment in the Azure CLI with the command below. Replace the SUBSCRIPTION-ID placeholder with the correct value.

     $ az account set --subscription SUBSCRIPTION-ID
    
  • Download the deployment template associated with your deployment using the command below. Replace the DEPLOYMENT-ID and RESOURCE-GROUP-ID placeholders with the correct values.

     $ az group deployment export --name DEPLOYMENT-ID --resource-group RESOURCE-GROUP-ID > template.json
    
  • Download the parameters file associated with your deployment using the command below. Replace the DEPLOYMENT-ID and RESOURCE-GROUP-ID placeholders with the correct values.

     $ az group deployment show --name DEPLOYMENT_ID --resource-group RESOURCE_GROUP_ID --query "properties.parameters" | sed '/"type":/d' > parameters.json
    
  • Redeploy the solution with the additional node(s) using the command below. Replace the DEPLOYMENT-ID and RESOURCE-GROUP-ID placeholders with the correct values, the ADMIN-PASSWORD and APP-PASSWORD placeholders with the same administration password and application password used when initially deploying the application, and the NUMBER-OF-SLAVES placeholder with the final number of nodes you wish to have in the cluster.

     $ az group deployment create --name DEPLOYMENT-ID --resource-group RESOURCE-GROUP-ID --template-file template.json --parameters @parameters.json --parameters adminPassword=ADMIN-PASSWORD appPassword=APP-PASSWORD '{"slaveCount": {"value": NUMBER-OF-SLAVES}}'
    

Verify that the new node(s) have been added successfully by logging in to the Azure portal and selecting the resource group and deployment to check the number of running nodes. Once you have confirmed that the new node(s) have been added successfully, log in to the primary node and verify that the new node(s) are now part of the cluster by following these instructions.

How to create a Virtual Network peering?

To connect two instances internally you can enable a Virtual Network (VNet) peering from the Azure Portal. Depending if the instances were launched in the same or in different resource groups, there are two methods for performing a internal connection: sharing a virtual network or enabling a virtual network peering.

How to connect to PostgreSQL from a different machine?

IMPORTANT: By default, the database port for the nodes in this solution cannot be accessed over a public IP address. As a result, you will only be able to connect to your database nodes from machines that are running in the same network. For security reasons, we do not recommend making the database port accessible over a public IP address. If you must make it accessible over a public IP address, we recommend restricting access to a trusted list of source IP addresses using firewall rules. Refer to the FAQ for information on opening ports in the server firewall.

Connecting to PostgreSQL from the same network

To connect to the PostgreSQL database from a different machine in the same network, use a command like the one below:

$ psql -h SERVER-IP -U postgres

You will be prompted to enter the postgres user password. This is the same password entered during the server deployment process.

If you wish to connect to the primary PostgreSQL host from a different network using its public IP address, we recommend creating an SSH tunnel, as described in the FAQ. However, you should only do this if you wish to temporarily connect to, or use, the PostgreSQL console. This approach is not recommended to permanently connect your application to the PostgreSQL cluster, as a connectivity failure in the SSH tunnel would affect your application's functionality.

Connecting to PostgreSQL from a different network

If you must connect to the database from a machine that it is not running in the same network as the PostgreSQL cluster, you can follow these approaches (these are shown in order of preference, from the most secure to the less recommended solution):

  • Option 1: Peer both virtual networks to secure the connections between the two instances. Learn how to connect instances in different networks using network peering.
  • Option 2: Create an SSH tunnel to connect the database console to perform administrative tasks using the primary host's public IP address. Refer to the FAQ for more information on this.

    NOTE: You should only access the primary server using an SSH tunnel if you wish to temporarily connect to, or use, the PostgreSQL console. This approach is not recommended to permanently connect your application to the PostgreSQL cluster, as a connectivity failure in the SSH tunnel would affect your application's functionality.
  • Option 3: Make the server publicly accessible and restrict access to a trusted list of source IP addresses using firewall rules. Refer to the FAQ for information on opening ports in the server firewall.

How to secure your server?

Once you have created a new database and user for your application, connect your applications to the PostgreSQL server using that database and password.

It is strongly recommended that you do not have empty passwords for any user accounts when using the server for any production work.

Allow remote access only from the database user associated with the database name by editing the pg_hba.conf file on the database server host as explained below.

  • Comment out the line that allow remote access to all users:

     #host all all all md5
    
  • Add a line for the new user:

     host DATABASE_NAME USER_NAME all md5
    
  • If your application will be connected from a fixed domain or IP address, you should update your pg_hba.conf file to only accept connections from that server instead:

     host DATABASE_NAME USER_NAME applicationserver.domain.com md5
    

    or

     host DATABASE_NAME USER_NAME IP_ADDRESS 255.255.255.255 md5
    
  • Change your postgres user password as explained in this section.

How to find the PostgreSQL database credentials?

How to connect to the PostgreSQL database?

You can connect to the PostgreSQL database from the same computer where it is installed with the psql client tool.

$ psql -U postgres

The default username for Multi-Tier Solutions is postgres. Check how to find your application credentials to get your password.

How to change the PostgreSQL root password?

You can modify the PostgreSQL password using the following command at the shell prompt:

$ psql -U postgres
postgres=# alter user postgres with password 'NEW_PASSWORD';
postgres=# \q

How to reset the PostgreSQL password?

If you don't remember your PostgreSQL database password, you can follow the steps below to reset it to a new value:

  • Change the authentication method in the configuration file at /opt/bitnami/postgresql/conf/pg_hba.conf on the database server host from md5 to trust and reload the configuration.

     $ sudo sed -ibak 's/^\([^#]*\)md5/\1trust/g' /opt/bitnami/postgresql/conf/pg_hba.conf
     $ sudo -u postgres pg_ctl reload
    
  • Connect to the PostgreSQL database and set the password to a new value:

     $ psql -U postgres
     postgres=# alter user postgres with password 'NEW_PASSWORD';
     postgres=# \q
    
  • Finally, change the authentication method back to md5 and reload the old PostgreSQL configuration:

     $ sudo sed -i 's/^\([^#]*\)trust/\1md5/g' /opt/bitnami/postgresql/conf/pg_hba.conf
     $ sudo -u postgres pg_ctl reload
    

You should now be able to connect to PostgreSQL with the new password.

How to create a database for a custom application?

These are the basic steps to create a new database and user for your applications:

  • Create a new role by executing the createuser command. With the options below, the new role will not be a superuser and will not have privileges for creating new databases or new roles (this is usually the default for the createuser command).

     createuser -U postgres USER_NAME -S -D -R -P
    

    You will be prompted to enter first the password for the new role and to reenter it, and then to enter the postgres role password.

  • Create a new database with the new role as the owner:

     createdb -U postgres DATABASE_NAME  -O USER_NAME
    

How to configure pgAdmin 4?

NOTE: This section assumes that you have downloaded and installed pgAdmin 4.

pgAdmin is the most popular and feature-rich platform for administration and development of PostgreSQL databases. Check the pgAdmin official page for more information.

To connect to your remote PostgreSQL database server using pgAdmin 4, follow these steps:

  • Make sure that you have your cloud server's IP address and application credentials (instructions).

  • Open port 5432 in the server firewall (instructions).

IMPORTANT: By default, the database port for the nodes in this solution cannot be accessed over a public IP address. As a result, you will only be able to connect to your database nodes from machines that are running in the same network. For security reasons, we do not recommend making the database port accessible over a public IP address. If you must make it accessible over a public IP address, we recommend restricting access to a trusted list of source IP addresses using firewall rules. Refer to the FAQ for information on opening ports in the server firewall.

  • Connect to your cloud server using PuTTY or another SSH client (instructions).

  • At the server console, edit the file /opt/bitnami/postgresql/data/pg_hba.conf and add the following at the end, then save the file:

    host all all all md5
    
  • Edit the file /opt/bitnami/postgresql/conf/postgresql.conf and replace this line

    listen_address='127.0.0.1'
    

    with:

    listen_addresses = '*'
    
  • Save the file.

  • Restart the PostgreSQL server:

    $ sudo service bitnami restart
    

Your PostgreSQL server is now configured to accept remote connections, and you can connect to it using pgAdmin 4. Follow these steps:

  • Launch pgAdmin 4.

  • Go to the "Dashboard" tab. In the "Quick Link" section, click "Add New Server" to add a new connection.

    pgAdmin 4 configuration

  • Select the "Connection" tab in the "Create-Server" window.

  • Then, configure the connection as follows:

  • Enter your server's IP address in the "Hostname/Address" field.

  • Specify the "Port" as "5432".

  • Enter the name of the database in the "Database Maintenance" field.

  • Enter your username as postgres and password (use the same password you used when previously configuring the server to accept remote connections) for the database.

  • Click "Save" to apply the configuration.

    pgAdmin 4 configuration

  • Check that the connection between pgAdmin 4 and the PostgreSQL database server is active. Navigate to the "Dashboard" tab and find the state of the server in the "Server activity" section:

pgAdmin 4 configuration

How to create a database backup?

To back up all the databases, create a dump file using the pg_dumpall tool.

 $ pg_dumpall -U postgres > backup.sql

This operation could take some time depending on the database size. You may be asked to enter your password multiple times, as the tool will connect to each database individually.

NOTE: The steps previously described will only back up the data contained inside your database. There may be other files that you should take into account when performing a full backup, such as files that may have been uploaded to your application. Refer to your application's documentation for more details.

How to restore a database backup?

Once you have the backup file, you can restore it with a command like the one below:

$ psql -U postgres -f backup.sql
azure-templates

Bitnami Documentation