Cloud servers from $4/mo - Grab the Deal! Let’s start ->
English
Community
Sign In
Sign In

How to Install PostgreSQL on Ubuntu

How to Install PostgreSQL on Ubuntu
Minhal Abbas
Technical writer
PostgreSQL Ubuntu
24.01.2025
Reading time: 7 min
Share

PostgreSQL is a well-known relational database management system that provides high-availability features. These are renowned for their functionalities, such as support for complex statements, views, triggers, and foreign keys. PostgreSQL is installed on Ubuntu to provide a secure as well as flexible DB infrastructure. By installing it on the Linux distribution, you can enable and deal with the vast data in an efficient and secure manner. This infrastructure is designed to deal with different workloads, from single-machine apps to large internet-facing apps with multiple concurrent users.

In this tutorial, we will walk through installing and setting up PostgreSQL on Ubuntu.

Installing PostgreSQL on Ubuntu

Ubuntu comes with the PostgreSQL package in its default repositories. You can install it on a Linux system following the given steps. It downloads the stable package. This is important for DB analysts, administrators, and industries that require a dependable DB solution for managing a large volume of data. 

Let’s move forward into each step of installing PostgreSQL with detailed explanations on Ubuntu.

Step 1: Refresh Ubuntu Repository

First, refresh the Ubuntu package indexes. It is useful for maintaining system stability and security:

sudo apt update

Step 2: PostgreSQL Installation 

PostgreSQL is included in Ubuntu's default repositories, making installation straightforward. To install it with some additional tools, execute the below command:

sudo apt install -y postgresql postgresql-contrib

Here:

  • postgresql: The core PostgreSQL DB server.
  • postgresql-contrib: Additional utilities and extensions useful for DB.

Step 3: Start and Enable Services

It is essential to enable the PostgreSQL service after installation and ensure it starts automatically upon bootup. The installed package utilizes the systemd daemon, which deals with the DB server runtime operations. 

Run the commands below to make sure the DB server automatically initializes when the server boots up.

Let’s initialize the service:

sudo systemctl start postgresql

And enable it so that it launches automatically when the machine boots up:

sudo systemctl enable postgresql

Image5

Step 4: Verify Installation

To verify if the installed package is operating properly, check out its status through the below:

sudo systemctl status postgresql

Image4

In the figure, you can see an active status. It represents the services that are correctly running.

Step 5: Access Shell

Finally, switch to the PostgreSQL user account and launch the CLI to interact with database operations. Here, the -i flag provides an interactive login shell:

sudo -i -u postgres

Image7

The above display gives access to a user account.

Once you're in the user environment, launch the CLI through the given command:

psql

Image6

As you can see in the above screenshot, this command launchs the CLI, where users can perform multiple operations by executing commands.

Step 6: Basic Database Commands

The creation of a database and a new user for any application is a good practice rather than using the root. In this way, a particular user can access the shell interface and run basic statements on the particular database. Here are general commands with thorough explanations and examples:

  1. Creation of the New Database

To prevent conflicts, ensure data integrity and control access privileges for different applications or users, you need to create a new database. For creation, use the given command within the shell:

CREATE DATABASE hostmandb;

Image10

The screenshot shows that a database has been created.

  1. Creation of the New User

Create a customized user and set a strong password:

CREATE USER hostman_user WITH ENCRYPTED PASSWORD 'Qwer1234';

Image8

We have created a hostman_user with the password Qwer1234.

  1. Give Privileges to the Created User

You can grant hostman_user complete privileges to the hostmandb through the given command. This allows controlled accessibility to database objects, enhancing data integrity as well as security:

GRANT ALL PRIVILEGES ON DATABASE hostmandb TO hostman_user;

Image15

  1. Exit Shell

For closing the shell, you can utilize the \q option as below:

\q

Image14

The figure shows that the shell has been exited.

Finally, you can exit the user environment by executing the given command:

exit

Image13

The output confirms that you have returned to the main system user account.

  1. Access a Database as the Particular User

You can connect to the particular database from the specific user account through the -U flag:

psql -U hostman_user -d hostmandb -h 127.0.0.1 -p 5432

Image16

Here:

  • -U hostman_user: Indicates the user.
  • -d hostmandb: Indicates the database name.
  • -h 127.0.0.1: Indicates the host (local machine).
  • -p 5432: Indicates the PostgreSQL default port.

In the given output, you can see that the user hostman_user has accessed the hostmandb console.

  1. Listing of All Databases

View all databases and check whether the hostmandb database is available or not:

\l

Image17

In the figure, you can see that the database hostmandb is listed.

  1. Switch/Connect to the Particular Database

To switch to a specific database, such as hostmandb, within PostgreSQL, use the \c command:

\c hostmandb

Image18

The above output confirms that the postgres user has successfully connected to the hostmandb database.

  1. Creation of the Particular Table

To create a customized table, use the below statement. In our example, we create a hostmantb table:

CREATE TABLE hostmantb(
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    department_id INTEGER
);

Image19

Here:

  • id SERIAL PRIMARY KEY: It creates an auto-incrementing primary key column with the name id.

  • name VARCHAR(100): It creates a name column that can store up to 100 characters.

  • age INTEGER: It creates an age column that stores integer values.

  • department_id INTEGER: This line creates a department_id column that stores integer values.

The outcome of the above screenshot confirms that the table hostmantb is successfully created with specific attributes.

  1. List All Tables in the Particular Database

For listing all tables in the hostmandb database, use the \dt command:

\dt

Image20

It displays all tables as well as confirms that the hostmantb is listed above.

  1. Overview a Table's Structure

You can view the structure of the created table through the \d command. Let’s display the structure of hostmantb table:

\d hostmantb

Image21

In the above figure, you can see the complete structure of hostmantb table with id, name, age, and department_id having specific types.

  1. Input Entries into a Particular Table

To input entries into a hostmantb table, use the below statement. This way, we input the values Peter, 35, and 1 to the columns name, age, and department_id, respectively:

INSERT INTO hostmantb(name, age, department_id) VALUES ('Peter', 35, 1);

Image22

The output confirms that we have successfully input data into the hostmantb table.

  1. Select All Data from a Particular Table

You can select all data from the hostmantb table through the below statement:

SELECT * FROM hostmantb;

Image23

  1. Modify Data in Particular Table

For modifying data in hostmantb, you can utilize the below statement. For example, set the age column to 40 for the row where the name column equals Peter:

UPDATE hostmantb SET age = 40 WHERE name = 'Peter';

Image9

The output confirms that the age column is updated.

  1. Remove Data from the Particular Table

In this section, you can remove data from the specific table through the DELETE statement. It deletes all entries from the hostmantb table where the value in the name column is Peter:

DELETE FROM hostmantb WHERE name = 'Peter';

Image12

In the screenshot, one entry from the hostmantb table is removed.

  1. Exit CLI

You can easily exit the CLI via the \q utility:

\q

Image11

This tutorial has given you thorough guidelines for every step involved in installing as well as setting PostgreSQL on Ubuntu. 

Conclusion

By installing PostgreSQL on Ubuntu, you can optimize the DB infrastructure. PostgreSQL is often chosen for its freely available nature, which allows for customization, the system's stability and security features. In addition, PostgreSQL's supporters' help and comprehensive guide make it simpler to enhance operations and troubleshoot problems. 

This combination makes sure that the DB system is both powerful as well as adaptable to several needs. In this tutorial, there are a lot of fundamental DB commands to get you started with database administration. Therefore, you will be able to create databases, and users and perform basic tasks.

PostgreSQL Ubuntu
24.01.2025
Reading time: 7 min
Share

Similar

PostgreSQL

PostgreSQL Triggers: Creation, Deletion, Examples

Triggers are used to instruct the PostgreSQL engine to execute a piece of code when a specific event occurs. They act like a catalyst for changes — a trigger that initiates a chain of events. A trigger must be associated with a specific table, view (pseudo-table), or foreign table. It activates only when operations are performed on that entity — INSERT, UPDATE, DELETE, or TRUNCATE. Depending on your needs, the trigger can run before, after, or instead of the event/operation. Types of Triggers PotgreSQL triggers are divided into two types depending on the level at which they operate: FOR EACH ROW: The function is called for each individual row affected by the event. For example, an UPDATE affecting 100 rows will trigger the function 100 times — once for each row. FOR EACH STATEMENT: The function is called just once per SQL statement, regardless of how many rows are affected. Triggers Use Cases Triggers are powerful tools with many use cases. Some examples include: Tracking Changes: You can use triggers to log transaction details when changes occur in a table. Data Validation: Triggers can enforce constraints before applying changes to the database. Auto-Filling Fields: Automatically populate fields based on new transaction data. Performance Optimization: For example, on a server like Hostman, you might log timestamps in a table and want to aggregate that data every 6 hours (four intervals a day). Scanning and aggregating all rows repeatedly is inefficient on large datasets. Instead of recalculating everything each time, you can use Materialized Views, which cache results. However, these are recalculated entirely on each refresh, which is still problematic on large datasets. Triggers can solve this by acting like "smart" materialized views: updating only the affected row, not the entire dataset. Creating a Trigger Let’s look at how to create a trigger in PostgreSQL. Syntax: CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] } ON table_name [ FROM referenced_table_name ] [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ] [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ] [ FOR [ EACH ] { ROW | STATEMENT } ] [ WHEN ( condition ) ] EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments ); Where the event can be one or more of the following: INSERT UPDATE [ OF column_name [, ...] ] DELETE TRUNCATE Key notes: You can create (CREATE) or replace (REPLACE) an existing trigger. You associate the function directly with a table/view/foreign table. The code runs only when the specified event occurs on that entity. Triggers using INSTEAD OF must be marked with FOR EACH ROW and are only allowed on views. Triggers using BEFORE or AFTER on views must be FOR EACH STATEMENT. Refer to PostgreSQL documentation for a summary table that outlines these rules. Basic Examples of PostgreSQL Triggers Trigger before update: CREATE TRIGGER check_update BEFORE UPDATE ON accounts FOR EACH ROW EXECUTE FUNCTION check_account_update(); Trigger before update of a specific column: CREATE OR REPLACE TRIGGER check_update BEFORE UPDATE OF balance ON accounts FOR EACH ROW EXECUTE FUNCTION check_account_update(); Trigger after update to log changes: CREATE TRIGGER log_update AFTER UPDATE ON accounts FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE FUNCTION log_account_update(); INSTEAD OF trigger for view insertions: CREATE TRIGGER view_insert INSTEAD OF INSERT ON my_view FOR EACH ROW EXECUTE FUNCTION view_insert_row(); Trigger on delete: CREATE TRIGGER example_delete_trigger AFTER DELETE ON my_view FOR EACH ROW EXECUTE PROCEDURE aft_delete(); Practical Example: Inserting Into Two Tables Let’s create a PostgreSQL trigger that adds information about a new employee to a second table when data is inserted into the primary table. Step 1: Create both tables: CREATE TABLE "Employee" ( "EmployeeId" INT NOT NULL, "LastName" VARCHAR(20) NOT NULL, "FirstName" VARCHAR(20) NOT NULL, "Title" VARCHAR(30), "ReportsTo" INT, "BirthDate" TIMESTAMP, "HireDate" TIMESTAMP, "Address" VARCHAR(70), "City" VARCHAR(40), "State" VARCHAR(40), "Country" VARCHAR(40), "PostalCode" VARCHAR(10), "Phone" VARCHAR(24), "Fax" VARCHAR(24), "Email" VARCHAR(60), CONSTRAINT "PK_Employee" PRIMARY KEY ("EmployeeId") ); CREATE TABLE "Employee_Audit" ( "EmployeeId" INT NOT NULL, "LastName" VARCHAR(20) NOT NULL, "FirstName" VARCHAR(20) NOT NULL, "UserName" VARCHAR(20) NOT NULL, "EmpAdditionTime" VARCHAR(20) NOT NULL ); Step 2: Create the trigger function: CREATE OR REPLACE FUNCTION employee_insert_trigger_fnc() RETURNS trigger AS $$ BEGIN INSERT INTO "Employee_Audit" ("EmployeeId", "LastName", "FirstName", "UserName", "EmpAdditionTime") VALUES (NEW."EmployeeId", NEW."LastName", NEW."FirstName", current_user, current_date); RETURN NEW; END; $$ LANGUAGE 'plpgsql'; Step 3: Create the trigger: CREATE TRIGGER employee_insert_trigger AFTER INSERT ON "Employee" FOR EACH ROW EXECUTE PROCEDURE employee_insert_trigger_fnc(); Test: Insert a record: INSERT INTO "Employee" VALUES(12, 'Smith', 'Jeff', 'Editor', 1, '1992-05-28 00:00:00', '2022-01-15 00:00:00', 'Paseo de Gracia', 'Barcelona', 'Catalonia', 'Spain', '128 665', '+15 52-469-2573', '+15 52-469-2573', 'mail@mail.com'); Check that the data is there: SELECT * FROM "Employee" WHERE "EmployeeId" = 12; Check the audit log: SELECT * FROM "Employee_Audit"; You should see something like: EmployeeId | 12 LastName | Smith FirstName | Jeff UserName | postgres EmpAdditionTime | 2025-04-10 Perfect! It works as expected. Modifying a Trigger To change a trigger's properties, use CREATE OR REPLACE TRIGGER and specify the same trigger function and table. You can update the remaining properties as needed. To rename a trigger: ALTER TRIGGER name ON table_name RENAME TO new_name; Check documentation for more details. Deleting a Trigger To delete a trigger: DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]; Example: DROP TRIGGER some_example_of_trigger ON "Example"; Note: You must own the table to delete its trigger. Use IF EXISTS to avoid errors if the trigger doesn’t exist. Use CASCADE to remove all dependent objects. Use RESTRICT to prevent deletion if dependencies exist (default). Check the documentation for more details. Important Notes You must have TRIGGER privilege on the table and EXECUTE privilege on the trigger function. You can check the system catalog pg_trigger to view existing triggers. If you define multiple triggers for the same table/event, they execute alphabetically by name.
11 April 2025 · 6 min to read
Kubernetes

How to Deploy PostgreSQL on Kubernetes

PostgreSQL is a popular relational database management system (RDBMS) that provides high-availability features like streaming replication, logical replication, and failover solutions. Deploying PostgreSQL on Kubernetes allows organizations to build resilient systems that ensure minimal downtime and data availability. With Kubernetes StatefulSets, you can scale PostgreSQL deployment in response to demand. Kubernetes Environment Setup To get started, make sure you have the following: Kubernetes Cluster (Cloud or Local):  You can set up a Kubernetes cluster on Hostman within no time. To follow this tutorial with a local Kubernetes cluster, you can use one of these tools: k3s, minikube, microk8s, kind. Kubectl: Kubectl allows users to interact with a Kubernetes cluster. The kubectl needs a configuration YAML file which contains cluster details and is usually provided by your cloud provider.  From the Hostman control panel, you can simply download this configuration file with a click of a button as indicated in the below screenshot. To connect, you need to set KUBECONFIG environment variable accordingly. export KUBECONFIG=/absolute/path/to/file/k8s-cluster-config.yaml Helm: You need Helm CLI to install Helm charts. Helm version 3 is required. Deploy PostgreSQL Using a Helm Chart Helm is a package manager for Kubernetes just like apt for Ubuntu and Debian. Instead of manually creating multiple YAML files for Pods, Services, Persistent Volumes, Secrets, etc., the Helm chart simplifies this to a single command (e.g., helm install), streamlining the deployment process. Step 1: Add helm repository To add the Bitnami PostgreSQL Helm repo, run this command: helm repo add bitnami https://charts.bitnami.com/bitnami To sync your local Helm repository with the remote one: helm repo update Step 2: Manage Data Persistence PostgreSQL requires persistent storage to ensure that data is preserved even if a pod crashes or is rescheduled. When a Persistent Volume Claim (PVC) is combined with a Persistent Volume (PV), Kubernetes can allocate a desired chunk of storage either in disk or cloud storage. PVC requests the Kubernetes cluster for storage space. Kubernetes then looks at the available PVs and assigns one to it. Create a file named postgres-local-pv.yaml with the YAML manifest: apiVersion: v1 kind: PersistentVolume metadata: name: postgresql-local-pv spec: capacity: storage: 5Gi accessModes: - ReadWriteOnce persistentVolumeReclaimPolicy: Retain storageClassName: manual hostPath: path: /mnt/data/postgresql This manifest creates a PersistentVolume backed by a local directory (/mnt/data/postgresql) on a specific node. This means if the node goes down or becomes unavailable, the data stored in that PV will be inaccessible, which is a critical risk in production. Therefore, it’s highly recommended to use cloud-native storage solutions instead of hostPath to ensure reliability, scalability and data protection. This PV has a reclaim policy of Retain, ensuring that it is not deleted when no longer in use by a PVC. You can set storageClassName to ceph-storage, glusterfs, portworx-sc, or openebs-standard based on your needs. Create a file named postgres-local-pvc.yaml with this text: apiVersion: v1 kind: PersistentVolumeClaim metadata: name: postgresql-local-pvc spec: accessModes: - ReadWriteOnce resources: requests: storage: 5Gi storageClassName: manual The ReadWriteOnce config means the volume can be read-write by a single node at a time. You might think, replacing it with ReadWriteMany will make your application highly available. This isn’t the case. ReadWriteMany (RWX) access mode allows multiple pods to access the same PersistentVolume simultaneously, this can indeed create serious issues leading to potential race conditions, data corruption, or inconsistent state. Apply these manifests using kubectl and create new resources. kubectl apply -f postgres-local-pv.yamlkubectl apply -f postgres-local-pvc.yaml Step 3: Install PostgreSQL Helm Chart Run the following command to install the Helm chart. helm install tutorial-db bitnami/postgresql --set auth.username=bhuwan \ --set auth.password=”AeSeigh2gieshe” \ --set auth.database=k8s-tutorial \ --set auth.postgresPassword=”Ze4hahshez6dop9vaing” \ --set primary.persistence.existingClaim=postgresql-local-pvc \ --set volumePermissions.enabled=true After a couple of minutes, verify if things have worked successfully with this command: kubectl get all Step 4: Test and Connect The following command runs a temporary PostgreSQL client pod. The pod connects to the database named k8s-tutorial, using the username bhuwan and the password from the environment variable $POSTGRES_PASSWORD. export POSTGRES_PASSWORD=$(kubectl get secret --namespace default tutorial-db-postgresql -o jsonpath="{.data.password}" | base64 -d) kubectl run tutorial-db-postgresql-client --rm --tty -i --restart='Never' \ --image docker.io/bitnami/postgresql:17.2.0-debian-12-r6 \ --env="PGPASSWORD=$POSTGRES_PASSWORD" \ --command -- psql --host tutorial-db-postgresql \ -U bhuwan -d k8s-tutorial -p 5432 After the session ends, the pod will be deleted automatically due to the --rm flag. A quick reminder, if you have changed the Helm chart release name, users, or database name, adjust the above commands accordingly. Deploy Postgres on Kubernetes from scratch A StatefulSet is the best Kubernetes resource for deploying stateful applications like PostgreSQL. This way, every PostgreSQL pod gets its own stable network identities and persistent volumes. Note: you’ll be using a previously created Persistent Volume Claim (PVC) and Persistent Volume(PV). So, do some cleanup and recreate those resources. helm delete tutorial-db kubectl delete pvc postgresql-local-pvc kubectl delete pv postgresql-local-pv kubectl apply -f postgres-local-pv.yaml -f postgres-local-pvc.yaml Create a file named postgres-statefulset.yaml with the following text: apiVersion: apps/v1 kind: StatefulSet metadata: name: postgres-statefulset labels: app: postgres spec: serviceName: "postgresql-headless-svc" replicas: 1 selector: matchLabels: app: postgres template: metadata: labels: app: postgres spec: containers: - name: postgres image: postgres:17.2 envFrom: - secretRef: name: postgresql-secret ports: - containerPort: 5432 name: postgresdb volumeMounts: - name: pv-data mountPath: /var/lib/postgresql/db volumes: - name: pv-data persistentVolumeClaim: claimName: postgresql-local-pvc Before you can apply these changes, create a new Secret for handling sensitive details like passwords with kubectl. kubectl create secret generic postgresql-secret --from-literal=POSTGRES_PASSWORD=Ze4hahshez6dop9vaing kubectl apply -f postgres-statefulset.yaml If the pod gets stuck with Pending state, you can try creating a StorageClass with the following manifest. kind: StorageClass apiVersion: storage.k8s.io/v1 metadata: name: manual provisioner: kubernetes.io/no-provisioner volumeBindingMode: WaitForFirstConsumer To investigate any further issues with the pod, you can use the command: kubectl describe pod postgres-statefulset-0 This command will report any issues related to scheduling the pod to a node, mounting volumes, or resource constraints. Databases like PostgreSQL are typically accessed internally by other services or applications within the cluster, so it's better to create a Headless service for it. Create a file called postgres-service.yaml and include the following YAML manifest: apiVersion: v1 kind: Service metadata: name: postgresql-headless-svc spec: type: ClusterIP selector: app: postgres ports: - port: 5432 targetPort: 5432 clusterIP: None Finally, you can test the connection with kubectl run. kubectl run tutorial-db-postgresql-client --rm --tty -i --restart='Never' \ --image docker.io/bitnami/postgresql:17.2.0-debian-12-r6 \ --env="PGPASSWORD=Ze4hahshez6dop9vaing" \ --command -- psql --host postgres-statefulset-0.postgresql-headless-svc \ -U postgres -p 5432 Scale, Replication, and Backup To scale up a Statefulset, simply pass the number of replicas with --replicas flag.  kubectl scale statefulset postgres-statefulset --replicas=3  To reach replicas, you can make use of headless service. For instance, with hostname postgres-statefulset-1.postgresql-headless-svc you can send requests to pod 1. For handling backups, you can use CronJob with the pg_dump utility provided by PostgreSQL. Best Practices Throughout the tutorial, the decision to handle passwords via Kubernetes Secret, using StatefulSet instead of Deployment was a good move. To make this deployment even more secure, reliable, and highly available, here are some ideas: Set Resource Requests and Limits: Set appropriate CPU and memory requests and limits to avoid over-provisioning and under-provisioning. Backups: Use Kubernetes CronJobs to regularly back up your PostgreSQL data. Consider implementing Volume Snapshots as well. Monitoring and Log Postgresql: You can use tools like Prometheus and Grafana to collect and visualize PostgreSQL metrics, such as query performance, disk usage, and replication status. Use Pod Disruption Budgets (PDBs): If too many PostgreSQL pods are disrupted at once (e.g., during a rolling update), it can lead to database unavailability or replication issues. Conclusion Helm chart is the recommended way of complex and production deployment. Helm provides an automated version manager alongside hiding the complexities of configuring individual Kubernetes components. Using the Helm template command, you can even render the Helm chart locally and make necessary adjustments with its YAML Kubernetes manifests. Kubernetes provides scalability, flexibility, and ease of automation for PostgreSQL databases. By leveraging Kubernetes features like StatefulSets, PVCs, PDBs, and secrets management, you can ensure that your PostgreSQL database is tuned for the production environment.
24 January 2025 · 8 min to read
PostgreSQL

How to Set Up Physical Streaming Replication with PostgreSQL on Ubuntu

Streaming replication is a common method for horizontally scaling relational databases. It involves one or more copies of the same database cluster operating on different devices. The primary database cluster handles both read and write operations, while the replicas are read-only. We can also use streaming replication to provide high availability: if the primary database cluster or server fails unexpectedly, the replicas can continue handling read operations, or one of them can be promoted to become the new primary cluster. PostgreSQL, a popular relational database, supports both logical and physical replication: Logical replication streams high-level changes from the primary cluster to replicas, allowing you to replicate changes to a single database or table. Physical replication, on the other hand, streams changes from the Write-Ahead Log (WAL) files, copying the entire cluster's state rather than specific areas. This method ensures that all changes to the primary cluster are replicated. This guide will help you set up physical streaming replication with PostgreSQL on Ubuntu 22.04 across two separate devices, each running PostgreSQL 17 clusters. One device will host the primary cluster, and the other will serve as the replica. Hostman offers a cloud PostgreSQL for your projects.  Prerequisites To follow this tutorial, you will need: Two separate devices running Ubuntu 22.04: One will act as the primary server and the other as the replica. Firewall settings that allow HTTP/HTTPS traffic and traffic on port 5432 (the default port for PostgreSQL 17). PostgreSQL 17 installed and running on both servers. Step 1: Configuring the Primary Database to Accept Connections The first step is to configure the primary database to allow connections from the replica(s). By default, PostgreSQL only accepts connections from localhost (127.0.0.1). To change this behavior, you need to modify the listen_addresses configuration parameter in the primary database. On the primary server, open the PostgreSQL configuration file postgresql.conf, located in the /etc/postgresql/17/main/ directory: sudo nano /etc/postgresql/17/main/postgresql.conf Once the file is open, find the listen_addresses variable and change its value from localhost to the IP address of the primary server. Remove the # symbol at the beginning of the line as well: listen_addresses = 'your_primary_IP_address' Save the changes and exit the file. The primary database is now ready to accept connections from other devices using the specified IP address. Next, you need to create a user role with the appropriate permissions that the replica will use to connect to the primary database. Step 2: Creating a Replication Role with Permissions Next, you need to create a dedicated role in the primary database with permissions for database replication. The replica will use this role to connect to the primary database. Creating a specific role for replication is crucial for security, as the replica will only have permission to copy data, not modify it. Connect to the database cluster: Log in as the postgres user by running: sudo -u postgres psql Create a replication role: Use the CREATE ROLE command to set up a role for replication: CREATE ROLE test WITH REPLICATION PASSWORD 'testpassword' LOGIN; This will output: CREATE ROLE We have now created the test role with the password testpassword, which has replication permissions for the database cluster. Configure access for replication: PostgreSQL has a special pseudo-database, replication, which replicas use to connect. To allow access, edit the pg_hba.conf file. Exit the PostgreSQL prompt by typing: \q Then open the configuration file using nano or your preferred editor: sudo nano /etc/postgresql/17/main/pg_hba.conf Add a rule for the replica: Append the following line to the end of the pg_hba.conf file: host  replication   test  your-replica-IP/32  md5 host: Enables non-local connections over plain or SSL-encrypted TCP/IP sockets. replication: Specifies the special pseudo-database used for replication. test: Refers to the previously created replication role. your-replica-IP/32: Restricts access to the specific IP address of your replica. md5: Sets the authentication method to password-based. If you plan to create multiple replicas, repeat this step for each additional replica, specifying its IP address. Restart the primary database cluster: To apply these changes, restart the primary cluster: sudo systemctl restart postgresql@17-main If the primary cluster restarts successfully, it is properly configured and ready to stream data once the replica connects. Next, proceed with configuring the replica cluster. Step 3: Backing Up the Primary Cluster to the Replica During the setup of physical replication with PostgreSQL, you need to perform a physical backup of the primary cluster’s data directory to the replica’s data directory. Before doing this, you must clear the replica’s data directory of all existing files. On Ubuntu, the default data directory for PostgreSQL is /var/lib/postgresql/17/main/. To find the data directory, you can run the following command on the replica database: SHOW data_directory; Once you locate the data directory, run the following command to clear all files: sudo -u postgres rm -r /var/lib/postgresql/17/main/* Since the files in the default data directory are owned by the postgres user, you need to run the command as postgres using sudo -u postgres. Note: If a file in the directory is corrupted and the command does not work (this is very rare), you can remove the main directory entirely and recreate it with the correct permissions: sudo -u postgres rm -r /var/lib/postgresql/17/mainsudo -u postgres mkdir /var/lib/postgresql/17/mainsudo -u postgres chmod 700 /var/lib/postgresql/17/main Now that the replica’s data directory is cleared, you can physically back up the primary server’s data files. PostgreSQL provides a useful utility called pg_basebackup to simplify this process. It even allows you to promote the server to standby mode using the -R option. Run the following pg_basebackup command on the replica: sudo -u postgres pg_basebackup -h primary-ip-addr -p 5432 -U test -D /var/lib/postgresql/17/main/ -Fp -Xs -R -h: Specifies the remote host. Enter the IP address of your primary server. -p: Specifies the port number for connecting to the primary server. By default, PostgreSQL uses port 5432. -U: Specifies the user role to connect to the primary cluster (the role created in the previous step). -D: Specifies the backup's destination directory, which is your replica's cleared data directory. -Fp: Ensures the backup is output in plain format (instead of a tar file). -Xs: Streams the contents of the WAL file during the backup from the primary database. -R: Creates a file named standby.signal in the replica’s data directory, signaling that the replica should operate in standby mode. It also adds the connection information for the primary server to the postgresql.auto.conf file. This configuration file is read each time the standard postgresql.conf is read, but the values in the .auto.conf file override those in the regular configuration file. When you run this command, you will be prompted to enter the password for the replication role created earlier. The time required to copy all the files depends on the size of your primary database cluster. At this point, your replica now has all the necessary data files from the primary server to begin replication. Next, you need to configure the replica to start in standby mode and proceed with replication. Step 4: Restarting and Testing Clusters After successfully creating a backup of the primary cluster’s data files on the replica, you need to restart the replica database cluster and switch it to standby mode. To restart the replica, run the following command: sudo systemctl restart postgresql@17-main Once the replica has restarted in standby mode, it should automatically connect to the primary database cluster on the other machine. To check whether the replica is connected and receiving the stream from the primary server, connect to the primary database cluster with the following command: sudo -u postgres psql Next, query the pg_stat_replication table on the primary cluster as follows: SELECT client_addr, state FROM pg_stat_replication; The output should look something like this: client_addr  | state----------------+-----------your_replica_IP | streaming If you see this result, the streaming replication from the primary server to the replica is correctly set up. Conclusion You now have two Ubuntu 22.04 servers with PostgreSQL 17 clusters, and streaming replication is configured between the servers. Any changes made in the primary database cluster will be reflected in the replica cluster. You can add more replicas if your databases need to handle higher traffic. To learn more about physical streaming replication, including how to configure synchronous replication to prevent the loss of critical data, refer to the official PostgreSQL documentation.
20 December 2024 · 8 min to read

Do you have questions,
comments, or concerns?

Our professionals are available to assist you at any moment,
whether you need help or are just unsure of where to start.
Email us
Hostman's Support