Managing PostgreSQL Extensions

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.

Databases tend to grow beyond their original filesystem over time. If they share the same partition as the operating system, it could potentially lead to I/O conflicts. Devices like network block storage RAID provide redundancy and enhance scalability. Regardless of your goals—whether increasing space or optimizing performance—this guide will assist you in moving the PostgreSQL data directory. Prerequisites To follow this guide, you will need: An Ubuntu 22.04 VPS server with a non-privileged user account and sudo privileges. PostgreSQL installed on your server. In this guide, we will move the data to a block storage device mounted at /mnt/volume_01. The method described here is universal and will help relocate the data directory to another location in any basic storage. Step 1. Move the PostgreSQL Data Directory First, check the current location of the data directory by starting an interactive PostgreSQL session. Here, psql is the command to enter the interactive monitor, and -u postgres instructs sudo to run psql as the system user postgres: sudo -u postgres psql In the PostgreSQL command line, enter this command to show the current directory: SHOW data_directory; By default, the directory is /var/lib/postgresql/xx/main, where xx is your PostgreSQL version. Exit the PostgreSQL prompt by typing \q and pressing ENTER. Before making any changes to the directory, stop PostgreSQL to avoid compromising data integrity: sudo systemctl stop postgresql You won’t be able to check the service status directly through systemctl after stopping it. To ensure the service has been stopped, run the following command: sudo systemctl status postgresql The last line of the output will confirm that PostgreSQL has indeed stopped. To copy the directory to the new location, use the rsync command. You can add flags: -a preserves the permissions and other attributes of the directory, while -v ensures detailed output so you can track progress. To replicate the original directory structure in the new location, run rsync from the postgresql directory. Creating this postgresql directory at the mount point and preserving ownership by the PostgreSQL user will prevent permission issues during future updates. Note: If tab completion is enabled, make sure the directory doesn't have a trailing slash. Otherwise, rsync will only copy the directory's contents to the mount point, not the directory itself. Strictly speaking, the versioned directory (e.g., 16) is unnecessary since the location is explicitly defined in the postgresql.conf file. However, it is recommended to follow the project's conventions, especially if you later need to run multiple versions of PostgreSQL: sudo rsync -av /var/lib/postgresql /mnt/volume_01 Once the copy is complete, rename the original folder with a .bak extension, and don't delete it until the move is complete. This ensures that nothing gets mixed up due to directories with the same name: sudo mv /var/lib/postgresql/16/main /var/lib/postgresql/10/main.bak Now, we can configure PostgreSQL to access the data directory in the new location. Step 2. Point to the New Location of the Directory By default, the value for data_directory is set to /var/lib/postgresql/16/main in the file /etc/postgresql/16/main/postgresql.conf. You need to edit this file to point to the new directory: sudo nano /etc/postgresql/16/main/postgresql.conf Now, locate the line starting with data_directory and change the path to point to the new location. The updated directive will look something like this: # /etc/postgresql/16/main/postgresql.conf...data_directory = '/mnt/volume_01/postgresql/10/main'... Save the file and close it by pressing CTRL+X, then Y, and finally ENTER. No further configuration is needed for PostgreSQL in the new directory. The only thing left to do at this point is restart the PostgreSQL service and verify that it correctly points to the new data directory. Step 3. Restart PostgreSQL After changing the data_directory directive in the postgresql.conf file, start the PostgreSQL server using systemctl: sudo systemctl start postgresql Check the server status: sudo systemctl status postgresql If the service started correctly, you will see a line like this at the end of the output. Finally, to ensure that the new directory is being used, open the PostgreSQL command line: sudo -u postgres psql Check the value of the data directory again: SHOW data_directory; The output will confirm that PostgreSQL is using the new data directory location. After verifying that everything is working properly, ensure you have access to your database and can interact with its data without issues. Once you are confident that the existing data is intact, you can delete the backup directory: sudo rm -Rf /var/lib/postgresql/16/main.bak Thus, you have successfully moved the PostgreSQL data directory to a new location. Conclusion If you followed the instructions correctly, your database directory is now in a new location, and you're closer to being able to scale your storage. Congrats!

This guide explains how to delete PostgreSQL databases using two methods: the SQL command DROP DATABASE and the dropdb utility. Both methods are effective, but dropdb offers slightly more options for managing the deletion process. Method 1: DROP DATABASE The first way to delete a PostgreSQL database is to use the SQL command: DROP DATABASE <database_name>; This command deletes the directory containing the database's information and previously stored records. Note the following: Only the database owner can execute DROP DATABASE. If someone is connected to the database at the time of execution, the deletion will not proceed. Let’s walk through deleting a database step by step. Step 1: Connect to PostgreSQL. Access PostgreSQL through the terminal: sudo -i -u postgres psql Step 2: Create a Test Database: CREATE DATABASE example_db; Step 3: Verify the Database Exists. List all databases to confirm we have successfully created the new one: \l This will display a table listing all databases on the server. Step 4: Delete the Database: DROP DATABASE example_db; Step 5: Verify Deletion. List all databases again to ensure example_db is no longer in the list: \l The table should no longer include the deleted database. IF EXISTS The IF EXISTS option allows you to check if a database exists before attempting to delete it. The full syntax is as follows: DROP DATABASE IF EXISTS <database_name>; If the database exists, the command will delete it. If no database with that name exists, an informational message will be displayed instead of an error. First, create a sample database: CREATE DATABASE hostman_db; Then, delete the newly created database using the IF EXISTS option: DROP DATABASE IF EXISTS hostman_db; If the database exists, the result will be the same as using DROP DATABASE. The hostman_db database will no longer be available. If you run the DROP DATABASE IF EXISTS command again with the same database name: DROP DATABASE IF EXISTS hostman_db; The terminal will display an informational message indicating that the entity doesn't exist. Finally, using DROP DATABASE without the IF EXISTS option will result in a less informative error message: DROP DATABASE hostman_db; WITH (FORCE) The WITH (FORCE) option is available in PostgreSQL version 13 and above. It is used for "forcefully" removing a database that is currently in use. The WITH (FORCE) option forcibly closes the session and deletes the database. DROP DATABASE <database_name> WITH (FORCE); Method 2: dropdb The dropdb command-line utility is essentially a wrapper around the DROP DATABASE SQL command. These two methods are identical in terms of functionality, but dropdb offers additional features that make the process of deleting a PostgreSQL database more convenient and informative. Its basic syntax is: dropdb <connection parameters> <options> <database_name> Flags and Options: Flag Description -e --echo Displays all the commands actually executed on the server while using the dropdb utility. -f --force Terminates all current connections before deleting the database. -i --interactive Prompts for confirmation before deleting the database. -V --version Displays the version of the dropdb utility. --if-exists Provides an informative message instead of an error if the target database does not exist. -? --help Displays the help menu with information about available options. -h <host> --host=<host> Specifies the host where the database is running. -p <port> --port=<port> Specifies the port number the server is listening to. -U <username> --username <username> Allows connection to the database using the specified username. -w --no-password Makes the password prompt optional. Useful for batch jobs and scripts. -W --password Forces a password prompt for every operation. --maintenance-db=<database_name> Specifies the name of the maintenance database for the connection. If you have cloud databases and need to delete unused entities periodically, you might want the process to be safe and clear to all users. The dropdb utility handles both of these requirements. For example, consider the following command: dropdb -i -e example_db It will: First prompt you to confirm the deletion of example_db. Display the commands executed during the operation. Conclusion To delete a database, you must be the owner of the database and have access to the terminal with sudo privileges. The choice of deletion method depends on you. There is no fundamental difference between using DROP DATABASE and the dropdb utility. It is simply a matter of convenience, as well as the specific scenarios in which you need to delete a database in PostgreSQL. Hostman offers a high-performance cloud PostgreSQL database solution to meet your needs.