Logical Replication in PostgreSQL

It is possible that you are going to need to move a database from one PostgreSQL server to another. Although it may appear complicated, PostgreSQL migration is possible with PostgreSQL's built-in utilities. This article outlines various methods to transfer a PostgreSQL database from one server to another on Ubuntu 22.04. Visualization of moving PostgreSQL database Prerequisites To migrate a Postgres database, you will need: A current server running Ubuntu 22.04 with PostgreSQL installed. The database to be transferred should already exist in PostgreSQL. A new cloud server or virtual machine with Ubuntu 22.04 and affordable cloud PostgreSQL pre-installed. This article uses PostgreSQL version 15. On Hostman, you can easily deploy such a server by choosing the PostgreSQL image when creating a server. Optionally, the pgAdmin client application for connecting and managing PostgreSQL databases. PgAdmin can be installed on any OS, including Windows, Linux, and macOS. Preparation Before Database Transfer Before starting the Postgres migration, ensure that both servers have: A network connection between them. Use tools like ping and telnet to verify this (with telnet, check ports 22 and 5432). Ports 22 (SSH) and 5432 (PostgreSQL) open. Enough free disk space. Configuring PostgreSQL for Remote Connections Ensure PostgreSQL can accept remote connections on both servers: Edit the postgresql.conf file. If using a different version, replace 15 with your version number: nano /etc/postgresql/15/main/postgresql.conf Find the listen_addresses parameter. By default, it is commented out and accepts only local connections (localhost). Allow remote connections from specific addresses or all addresses (for testing purposes, use *): listen_addresses = '*' In production environments, specify only the required addresses.  Save and exit the file. Edit the pg_hba.conf file: nano /etc/postgresql/15/main/pg_hba.conf Find the line for IPv4 local connections (# IPv4 local connections) and update it to allow connections from specific addresses or all addresses for testing: host all all 0.0.0.0/0 md5 Save and exit the file. Restart the PostgreSQL server: systemctl restart postgresql Set a strong password for the PostgreSQL user on both servers: sudo -i -u postgres psql In the psql console run: ALTER USER postgres WITH PASSWORD 'nsH7z*0kl>&7?7'; Where postgres is the username and nsH7z*0kl>&7?7 is the password. Transferring the Database Using pg_dump and psql Typically, transferring a database involves three steps: Creating a backup of the database. Transferring the backup to the target server. Restoring the database on the new server. These steps can be performed using PostgreSQL's pg_dump and psql tools. Transferring a Database Let's take an example of moving a database called e_commerce from a server equipped with IP address 166.1.227.252 to a server equipped with IP address 91.206.179.207. Create the target database beforehand: CREATE DATABASE e_commerce; Then run: pg_dump -h localhost -U postgres e_commerce | psql -h 91.206.179.207 -U postgres e_commerce Explanation: pg_dump creates a backup of the database. -h localhost specifies the database server address. -U postgres specifies the username. e_commerce is the database name on the current and new server.  psql connects to the remote PostgreSQL server and loads the database. -h 91.206.179.207 specifies the target server address. Transferring Without Remote Access If remote access is unavailable, save the database to a file, transfer it using scp, and restore it on the target server: pg_dump -h localhost -U postgres e_commerce > e_commerce.sql && scp e_commerce.sql db-admin@91.206.179.207:/var/lib/postgresql When executing the command, the password for the postgres system user will be requested, not the password for the postgres user defined within the database. Where: pg_dump creates a database backup; -h localhost is the address of the server (IP address or domain name) where the database is located. In this example, the database server address matches the server address itself. If the database is on another server and network access is available, you can specify the address of the remote database; -U postgres is the username used to connect to the database; e_commerce is the name of the database to be transferred; e_commerce.sql is the name of the file in .sql format where the database will be saved; scp is a utility for secure file copying between hosts. It uses the SSH protocol for data transfer and protection; db-admin@91.206.179.207:/var/lib/postgresql means username_on_remote_server@address_of_remote_server:full_path where the backup file will be saved. After entering the command, you first need to enter the password for the database user account (in this example, it is the postgres user), and then enter the password for the remote server user (in this example, it is the db-admin user). Now you need to upload the file to the database. Run these commands on the target server. Create a database in psql: CREATE DATABASE e_commerce; Then, exit psql and run in the terminal: psql e_commerce < e_commerce.sql Creating a Compressed Archive For larger databases, create a compressed archive: pg_dump -h localhost -U postgres e_commerce > e_commerce.tar.gzip && scp e_commerce.tar.gzip db-admin@91.206.179.207:/var/lib/postgresql Restore from the archive: psql e_commerce < e_commerce.tar.gzip Adding a Timestamp to the Archive Name You can include the precise date and time the database was backed up in the file name if you need to know that information. To do this, use the date command and the date format. The example below will use the day-month-year date format: pg_dump -h localhost -U postgres e_commerce > e_commerce_$(date +%d-%m-%y).sql Transferring the Database Using pgAdmin Alternatively, you can use pgAdmin's graphical interface for the Postgres database migration. Backup Database Launch pgAdmin: Open pgAdmin and connect to your PostgreSQL server. Register Server: Right-click on Object Explorer, select Register, then Server. Configure Connection: Name: In the General tab, enter a name for the connection (e.g., my_db). Next, go to the Connection tab and specify: Host name/address: Specify the IP address or domain name of the PostgreSQL server. Port: Default is 5432; change if needed. Maintenance database: Name of the database for backup. Username and Password: Enter credentials for database access. Connect: Click Save to connect. If successful, the database appears on the left sidebar. Backup Database: Right-click on the database name and select Backup.   Set a Filename for the backup file. Choose a Format and Encoding (UTF8 recommended). Select specific Objects to include. Click Backup to start. Restore Database Prepare New Database: Open psql shell. Execute: CREATE DATABASE e_commerce; Connect to PostgreSQL Server: In pgAdmin, connect to the new PostgreSQL server, selecting e_commerce as the database. Restore Database: Right-click on the database name and choose Restore. Set the Format (ensure it matches the backup file). Specify the Filename of the backup file. Click Restore to begin. Wait for the Process completed confirmation. A quick representation of terminal when moving PostgreSQL Database Conclusion PostgreSQL offers several methods to migrate databases between servers, including using built-in tools for flexible and robust database backups and transfers. If you've ran into some troubles, check our instruction on how to create server on Ubuntu. And if you’re looking for a reliable, high-performance, and budget-friendly solution for your workflows, Hostman has you covered with Linux VPS Hosting options, including Debian VPS, Ubuntu VPS, and VPS CentOS. Frequently Asked Questions (FAQ) How do I move a Postgres database to another server?  The most common method for smaller databases is using pg_dump to export the data and psql to import it. Export (Source): pg_dump -U username -h localhost dbname > backup.sql Transfer: Copy the backup.sql file to the new server (e.g., via scp). Import (Target): psql -U username -h localhost -d new_db < backup.sql What are the three main DB migration strategies? Big Bang (Offline): The entire system is taken offline, data is exported, moved, and imported. It is simple but requires significant downtime. Trickle (Replication/Zero-Downtime): The new database is set up as a replica of the old one. Once they are synchronized, you switch the application to the new DB. Dual-Write: The application is modified to write data to both the old and new databases simultaneously during the transition period. PostgreSQL database migration checklist  Before switching over, ensure you have verified: Versions: Are the source and target Postgres versions compatible? (Newer targets can usually read older dumps, but not vice-versa). Extensions: Are all required extensions (like PostGIS or pgcrypto) installed on the target server? Users & Roles: Have you migrated the global users? (Standard pg_dump does not include users; use pg_dumpall --globals-only). Connectivity: Is the firewall on the new server configured to accept connections from your application? Collation/Encoding: Do both servers use the same locale (e.g., UTF-8) to prevent data corruption? How do I migrate users and passwords?  Since pg_dump only backs up a specific database, it skips global data like users. To migrate roles, run: pg_dumpall -U postgres --globals-only > globals.sql Then restore this file on the new server before importing your database. What is the difference between pg_dump and pg_basebackup? pg_dump: Creates a logical backup (SQL commands). It is portable and works across different OS/versions but is slower for massive datasets. pg_basebackup: Creates a physical binary copy of the database files. It is faster for large databases but requires the OS and Postgres versions to be identical.

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. This also useful if you use dedicated servers. Choose your server now! 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. After scaling your StatefulSet, adjust CPU and memory quotas as shown in the Kubernetes Requests and Limits tutorial to prevent runaway queries from monopolizing node resources—ensuring predictable performance and avoiding OOMKilled errors across all replicas. 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. Choose your server now! 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. And if you’re looking for a reliable, high-performance, and budget-friendly solution for your workflows, Hostman has you covered with Linux VPS Hosting options, including Debian VPS, Ubuntu VPS, and VPS CentOS. Frequently Asked Questions (FAQ) Should you run Postgres in Kubernetes?  Yes, but with caution. While it was previously discouraged, modern tools (Operators) make it viable. It offers great benefits for automation and scalability, but it adds significant complexity compared to managed services (like RDS) or standard VM deployments. It is best suited for teams with strong Kubernetes expertise. What is the recommended architecture for PostgreSQL in Kubernetes?  The standard recommended architecture is a High Availability (HA) Primary-Replica setup. Primary: Handles writes and reads. Replicas: Handle read-only traffic and serve as failover candidates. Operators: Use a Kubernetes Operator (like CloudNativePG, Zalando, or Crunchy Data) to manage the failover, backups, and synchronization automatically, rather than managing raw StatefulSets manually. What operating system is recommended for Postgres?  PostgreSQL is developed primarily on Linux (Debian and Ubuntu are the most common distributions for the container images). Since Kubernetes runs on Linux, this is the native and most performant environment. How do I deploy PostgreSQL on Kubernetes?  For production, avoid manual YAML files. The best methods are: Helm Charts: For quick, standard deployments (e.g., Bitnami charts). Operators: For lifecycle management (backups, updates, HA). Command example: helm install my-postgres oci://registry-1.docker.io/bitnami/charts/postgresql How does storage work for Postgres in Kubernetes?  Postgres requires persistent storage so data survives pod restarts.You must configure a PersistentVolumeClaim (PVC) that maps to a PersistentVolume (PV) backed by your storage class (e.g., AWS EBS, Google Persistent Disk, or local storage). How do I access the Postgres database from outside the cluster?  By default, the database is only accessible within the cluster via ClusterIP. To access it externally, you can use: Port Forwarding: (For debugging) kubectl port-forward svc/my-postgres 5432:5432 LoadBalancer: Change the service type to LoadBalancer (for cloud environments). Ingress: Configure an Ingress controller (though typically Ingress is for HTTP, TCP ingress is possible).

One of the more sophisticated open-source relational database management systems (DBMS) is PostgreSQL. On Debian 11, it can be installed from either the official PostgreSQL repository or the operating system repository. In this article, you will learn both methods, as well as how to perform common operations like creating roles and databases. Don't forget to check how to configure static IP address on Debian. A quick scheme of how PostrgreSQL installation works Installation from the Debian 11 repository On Debian, you can install PostgreSQL directly from the system repository. First, update your package list. Launch the terminal and run: sudo apt update && sudo apt upgrade The PostgreSQL package is available in the Debian repository, so you can install it using the apt utility. To do this, run: sudo apt install postgresql postgresql-contrib Once the installation is complete, check the status of the service using the command: sudo systemctl status postgresql If the service does not start automatically, you can start it manually. To do this, run: sudo systemctl start postgresql To stop a running service, run: sudo systemctl stop postgresql Before configuring PostgreSQL on Debian, make sure the service is running. Cloud tip: For more control, start deploying with our free cloud databases to be more efficient and save money! Installation from the official PostgreSQL repository If you want to use only the latest versions of Postgres, we recommend using the official PostgreSQL repository for installation and subsequent updates. First of all, you need to add the GPG signing key. This is a security requirement to verify the authenticity of the PostgreSQL repository. To do this, launch a terminal and run: curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /usr/share/keyrings/postgresql-keyring.gpg Now you are ready to add the Postgres repository. Use the following command: echo "deb [signed-by=/usr/share/keyrings/postgresql-keyring.gpg] http://apt.postgresql.org/pub/repos/apt/bulseye-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql.list After successfully integrating the PostgreSQL library, you can install the DBMS. But before you do that, update the system repository using the command: sudo apt update After updating, run the following command to install PostgreSQL on Debian: sudo apt install postgresql Installation is completed. Now you can proceed to the basic configuration of PostgreSQL. Basic setup When installing Postgres, the postgres user is automatically created. You can use this account for your first connection. Switch to the postgres user: sudo su - postgres Run the psql utility which is a shell for managing PostgreSQL: psql You can now interact with the PostgreSQL server. To exit the shell, enter: \q You can use the following command to access the Postgres command line without switching users: sudo -u postgres psql However, the postgres user is usually only used from localhost. If, for example, you use cloud databases, it is better to create a new role for the connection. Creating a role and a database The createuser command allows you to create new roles from the command line. Only superusers and roles with CREATEROLE privileges can create new roles. In the example that follows, we will build a database called hostman_db and a new role called hostman. We will then give the new role the ability to handle the database. First create a new role: sudo su - postgres -c "createuser hostman" Then create a new database: sudo su - postgres -c "createdb hostman_db" To grant the user permissions to the database, connect to the shell: sudo -u postgres psql Run the following query to grant the hostman user privileges to manage the hostman_db database: GRANT ALL PRIVILEGES ON DATABASE hostman_db TO hostman; You can create new roles and databases in the PostgreSQL shell. In this case, the syntax will be slightly different. To create a new role with a password, run: create user cloud with password 'hostmancloud'; To create a new database, run: create database cloud_db; Then you must also grant all privileges with the GRANT ALL PRIVILEGES ON DATABASE … TO … command. Setting up remote access Only the local interface 127.0.0.1 is used by the Postgres server by default for listening. This might be a hassle. Suppose you have PostgreSQL installed on a server running on Hostman. Remotely connecting to it will be far more convenient. You must set up the server to listen to different network interfaces in order to accomplish this. To change the configuration, open the postgresql.conf file using any editor. This example uses the nano editor: sudo nano /etc/postgresql/12/main/postgresql.conf Find the CONNECTIONS AND AUTHENTICATION section and the line #listen_addresses = 'localhost' in the configuration file. Change the line value to listen_addresses = '*'. If you want the server to listen not to all network interfaces, but only to the selected one, specify it instead of an asterisk. Save the file and restart the Postgres service for the changes to take effect: sudo service postgresql restart The last step is to allow connections from the network. To install it, you need to edit the pg_hba.conf file. Open it in the editor: sudo nano /etc/postgresql/12/main/pg_hba.conf Find the IPv4 local connections line. Specify the desired network. For example, like this: TYPE DATABASE     USER ADDRESS                  METHOD host all hostman 38.62.228.244  md5 You can use other authentication methods. For a complete list, see the PostgreSQL documentation. Installing PostgreSQL on Debian is a simple and straightforward process Conclusion There are two ways to install managed PostgreSQL on Debian. The first option is to use the system repository. Its main advantage is speed. There is no need to install anything additional, just run one command. The downside is that the system repository does not always contain the latest version of the software. The second installation option is to use the official PostgreSQL repository. This method ensures that you are using the latest version of the DBMS. But you will have to perform a few more steps: first, add the official repository itself and only then install Postgres from it. Hostman can help you deploy NoSQL or SQL cloud database on one of the most popular engines in just seconds. With an intuitive interface and around-the-clock free support, deploying MySQL cloud or Postgres cloud becomes much easier.