How to Set Up Physical Streaming Replication with PostgreSQL on Ubuntu

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.

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 Step 4: Verify Installation To verify if the installed package is operating properly, check out its status through the below: sudo systemctl status postgresql 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 The above display gives access to a user account. Once you're in the user environment, launch the CLI through the given command: psql 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: 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; The screenshot shows that a database has been created. Creation of the New User Create a customized user and set a strong password: CREATE USER hostman_user WITH ENCRYPTED PASSWORD 'Qwer1234'; We have created a hostman_user with the password Qwer1234. 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; Exit Shell For closing the shell, you can utilize the \q option as below: \q The figure shows that the shell has been exited. Finally, you can exit the user environment by executing the given command: exit The output confirms that you have returned to the main system user account. 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 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. Listing of All Databases View all databases and check whether the hostmandb database is available or not: \l In the figure, you can see that the database hostmandb is listed. Switch/Connect to the Particular Database To switch to a specific database, such as hostmandb, within PostgreSQL, use the \c command: \c hostmandb The above output confirms that the postgres user has successfully connected to the hostmandb database. 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); 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. List All Tables in the Particular Database For listing all tables in the hostmandb database, use the \dt command: \dt It displays all tables as well as confirms that the hostmantb is listed above. 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 In the above figure, you can see the complete structure of hostmantb table with id, name, age, and department_id having specific types. 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); The output confirms that we have successfully input data into the hostmantb table. Select All Data from a Particular Table You can select all data from the hostmantb table through the below statement: SELECT * FROM hostmantb; 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'; The output confirms that the age column is updated. 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'; In the screenshot, one entry from the hostmantb table is removed. Exit CLI You can easily exit the CLI via the \q utility: \q 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 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.