Installing PostgreSQL on Debian

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

In PostgreSQL, version upgrading is performed in different ways: Installing packages with a new version of PostgreSQL. It is suitable only for minor updates. Before performing the update, study the release notes; Using the standard pg_dumpall program. It is a reliable method, but there may be a long downtime. Using the standard pg_upgrade program. Another quick way to upgrade, but errors may occur. Updating via logical replication. This option has minimal downtime but is only suitable for PostgreSQL versions greater than 10.0. Earlier versions require extensions. The choice depends on which release is used on the server and which version you want to upgrade to. In this article, we will look at all the above upgrade methods. If you have some troubles while using PostgreSQL, check our instruction on how to set up your database. Choose your server now! Important notes before upgrading The main thing is to understand the peculiarities of updates between different versions. The numbers consist of two digits, for example, 10.1. The first digit is the major version number (10). The second digit is the minor release number (1). Before PostgreSQL 10, the version numbers consisted of three digits. For example, in 9.6.3, 9.6 is the major release number and 3 is the minor version number. You must understand this difference to choose the right upgrade method. In minor versions, the data storage format does not change, so there are no compatibility problems. Therefore, the transition from PostgreSQL 10.1 to 10.6 can be carried very easily. To upgrade, turn off the server, replace the executable files, and start the server again. However, the documentation notes that some releases may require manual changes. Therefore, always read the release notes before upgrading. In major versions, the data format may change. This makes updating more difficult. You need to either unload the data and upload it again, use the pg_upgrade program, or use logical replication. We'll talk about all these methods below. Cloud tip: For more control, start deploying with our free cloud databases to be more efficient and save money! Upgrading within one major version Let's look at how to update Postgresql Version 14.1 to PostgreSQL 14.3 on an Ubuntu server. PostgreSQL is available on Ubuntu by default. Start with the command: sudo apt-get upgrade Debian and Ubuntu only release one version of PostgreSQL per OS release. For example, Debian Squeeze/6.0 only has PostgreSQL 8.4. If you need a different version of PostgreSQL, use packages from PGDG. If you want the latest version of PostgreSQL, you must first install the Postgres repository. Add a repository: sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' Import the signing key: wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - Update the list of packages in the system: sudo apt-get update Install the required Postgres version: sudo apt-get -y install postgresql-14.3 To view the list of installed DBMSs, run: dpkg --get-selections | grep postgres Also see the list of clusters: pg_lsclusters Stop PostgreSQL before making changes: sudo service postgresql stop When Postgres packages are installed, they create a default cluster for you. You should rename the new Postgres cluster so there are no conflicts with the old cluster name when upgrading. sudo pg_renamecluster 14.3 main main_pristine Upgrade the old cluster: sudo pg_upgradecluster 14.1 main Start the service: sudo service postgresql start Check the list of clusters again and make sure the new one is working: pg_lsclusters Get rid of the old cluster: sudo pg_dropcluster 14.1 main Upgrading via pg_dumpall If you need to change the major version, use the pg_dumpall program. The essence of this method is to unload data from one main version and then load it into another. Before unloading data, make sure that no changes are being made to the database right now. Otherwise, some of the changes may not be included in the final dump. Get the dump and write it to a file: pg_dumpall > output_file Stop the server: pg_ctl stop Change the name of the old directory to eliminate name conflicts: mv /usr/local/pgsql /usr/local/pgsql.old The old directory can simply be deleted. But it would be wiser to rename it to leave room for restoring data. Instead of /usr/local/pgsql, specify the path to your directory. Install the new version from source. There is a detailed guide in the documentation.  Form a new cluster: /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data Transfer all changes to the pg_hba.conf and postgresql.conf files. Start the database server: /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data Restore data from backup: /usr/local/pgsql/bin/psql -d postgres -f output_file The disadvantage of this method is that the server will be turned off for a long time. To reduce downtime, you can install the new server in a different directory and then run the old and new servers on different ports. To transfer data, use the command: pg_dumpall -p 5432 | psql -d postgres -p 5433 Instead of 5432 and 5433, specify the port numbers on which you ran the servers. Upgrading via pg_upgrade To avoid unloading and uploading data, use the pg_upgrade program. It helps you update faster. The pg_upgrade program creates system tables anew, taking into account changes in the PostgreSQL latest versions. In this case, the old data files are retained. In addition, pg_upgrade verifies that the clusters are compatible. The pg_upgrade program helps you upgrade from PostgreSQL 8.4.X to the current DBMS release. Let's look at a general upgrade plan using pg_upgrade. Move the old cluster. This must be done if the directory was not linked to the old release. For example, it is located at /usr/local/pgsql. If you do not rename it, a conflict will occur during the update. Renaming is performed when the server is turned off with the command: mv /usr/local/pgsql /usr/local/pgsql.old Build a new version from source. Adjust configure so that the flags are compatible with the old cluster configuration. Before starting the upgrade, pg_upgrade will check the compatibility of the flags. Install new executable files. To place the server in a non-standard directory, use prefix: make prefix=/usr/local/pgsql.new install Use initdb to initialize a new cluster. Check that the flags are compatible with the flags of the old cluster. Install extension shared object files. If updates are available, pg_upgrade will automatically create a script for their subsequent installation. Transfer full text search files. Configure peer authentication in pg_hba.conf. This needs to be done because pg_upgrade will connect to the old and new hosts multiple times. Check that the old and new servers are stopped: pg_ctl -D /opt/PostgreSQL/9.6 stoppg_ctl -D /opt/PostgreSQL/14 stop Run pg_upgrade from the new server. First, call the program in check mode by adding the flag: pg_upgrade --check In response, you will receive a list of adjustments that need to be made manually after the upgrade. If you made changes to the pg_hba.conf file, restore it to its previou state. Sometimes, other configurations on the new cluster need to be changed to match the settings of the old cluster. Start the server. If you are satisfied with the result, delete the old cluster. For details and features of using pg_upgrade, see the documentation. Using Logical Replication Starting from the 10th version of PostgreSQL, we have a method for logical replication of data objects and changes in them. It is based on the use of replication identifiers; usually, they are primary keys. Logical replication uses the publish-subscribe model. The user creates a snapshot of the published database and copies it to the subscriber. In the PostgreSQL documentation, one of the typical scenarios for using logical replication is precisely the replication between different major versions of PostgreSQL. The backup server can be located on the same or different host. After synchronization is complete, various options are available. For example, you can make the new server the main one and turn off the old one. The main advantage of logical replication is minimal downtime. Server interruption, in some cases, is limited to a few seconds. Choose your server now! Conclusion We looked at universal methods for upgrading PostgreSQL to a new major version and updating within one major version. Using this guide, you can select the method that works best for your particular case and update your current version of PostgreSQL. Hostman provides pre-configured and ready-to-use cloud databases, including cloud PostgreSQL. Frequently Asked Questions How to check PostgreSQL version on my server? You can run psql --version in the terminal or connect to your DB and run SELECT version();. How do I upgrade PostgreSQL to a new version? Use pg_dumpall to back up, install the new version, and restore your data. What is the difference between pg_upgrade and pg_dump? pg_upgrade is quicker and keeps configs. pg_dump is better if you want full control. Do I need to uninstall the old PostgreSQL version? Nope, you can keep both temporarily to ease migration.