How to Change the PostgreSQL Data Directory on Ubuntu

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

PostgreSQL offers a vast array of extensions designed to simplify solving complex and non-standard tasks. They allow you to enhance the capabilities of your database management system, bypass certain limitations, and streamline analysts' work. There are two types of extensions: Bundled extensions: These come with PostgreSQL (e.g., in the contrib package). Custom extensions: These are created by users based on their specific needs. Once downloaded and installed, custom functions work just like standard ones. Extensions enable the handling of temporal, spatial, and other data types. Remember: if you can't find a ready-made solution, you can always create one yourself. If you're using a cloud PostgreSQL database on Hostman, you can easily install several popular extensions directly from the control panel. Simply open your database page, navigate to Configuration → Modify, and enable the extensions you need. Installation and Management of Extensions Since the contents of the contrib package differ for each PostgreSQL version, start by checking which functions are available in your version. Viewing the List of Standard Extensions To list the extensions available for installation, the default version, the version of the installed application, and a brief description of their functions, run the following command: SELECT * FROM pg_available_extensions; Note: Some features are only accessible with a superuser account (postgres) or an account with installation privileges.  Installing Extensions Let's break down the command used to install any PostgreSQL extensions: CREATE EXTENSION IF NOT EXISTS extension_nameWITH SCHEMA schema_nameVERSION versionCASCADE; The command includes optional but helpful parameters that you can use during installation: IF NOT EXISTS: Checks if an extension with the specified name already exists. WITH SCHEMA: Specifies the schema where the extension will be installed. If not provided, it will be installed in the current schema. VERSION: Specifies the version to install. If not specified, the latest version will be installed. CASCADE: Automatically installs all additional extensions required for proper functioning. Important: After installation using this command, you need to make specific entries in the PostgreSQL configuration file and then restart the server. Updating Extensions A new version is almost always better than the old one, right? Developers refine the code, fix bugs, and introduce new features, making it important and beneficial to update extensions. To upgrade an extension to a specific version, use the following command: ALTER EXTENSION extension_nameUPDATE TO version; If we omit the version parameter, the latest version will be installed. Removing Extensions Sometimes, an extension is no longer needed, and you might want to free up memory for better use. You can remove an extension with the following command: DROP EXTENSION IF EXISTS extension_nameCASCADE | RESTRICT; Additional Parameters: IF EXISTS: Checks whether the extension exists before attempting to remove it. CASCADE: Automatically removes all objects that depend on the extension. RESTRICT: Prevents removal if other objects depend on the extension. Top Most Useful Extensions for PostgreSQL pg_stat_statements The pg_stat_statements extension helps identify queries that place a heavy load on the system, how often they are executed, and how long they take. This information is crucial for evaluating database performance, identifying bottlenecks, and optimizing processes. Given the large size of many databases, query execution time must be efficient. This extension provides the metrics to assess and improve performance. Example Usage The following command shows the SQL query (query), its total execution time in minutes (total_min), average execution time in milliseconds (avg_ms), and the number of times it was called (calls): SELECT query, (total_exec_time / 1000 / 60) AS total_min, mean_exec_time AS avg_ms, callsFROM pg_stat_statementsORDER BY 1 DESCLIMIT 10; pgcrypto If you’re interested in encrypting data in PostgreSQL, the pgcrypto extension is essential. It offers cryptographic functions for encrypting data, such as passwords. By default, it supports the following encryption algorithms: md5, sha1, sha224, sha256, sha384, and sha512. You can expand the list of supported algorithms by configuring the extension to use OpenSSL in its settings. btree_gist You need the btree_gist extension to leverage different types of PostgreSQL database indexes (B-tree and GiST). It is especially useful for databases containing spatial data, such as city or store coordinates. B-tree Index: The default index type in PostgreSQL. It can index any sortable data, such as numbers and dates. B-tree is efficient and versatile but unsuitable for unsortable data. GiST Index: Handles any type of data, including geospatial data. Key Feature: In addition to the typical search operators for B-tree indexes, btree_gist also supports the PostgreSQL "not equal" operator (<>). timescaledb Time-series data tracks changes over time, such as application requests, sales volumes, or weather temperatures. While specialized databases like InfluxDB or ClickHouse are designed for time-series data, they may not handle other data types effectively. In such cases, the timescaledb extension for PostgreSQL offers a convenient alternative. timescaledb enables the storage and processing of time-series data directly in PostgreSQL. To use it: Download the appropriate version from the official website. Follow the installation instructions. Add the extension with the CREATE EXTENSION command. hstore The hstore extension allows PostgreSQL to store key-value pairs in a single data field. This is similar to data structures found in object-oriented programming languages like Python. With hstore, you can store grouped data without requiring additional database columns. For example, in a bookstore database, a single column could hold attributes such as the number of pages, genre, and illustration details for each book. Example Usage: Create a table with an hstore column: CREATE TABLE books (    id serial PRIMARY KEY,    name varchar,    attributes hstore); Insert data into the table: INSERT INTO books (name, attributes) VALUES (    'Harry Potter and the Philosopher''s Stone',    'author => "J. K. Rowling", pages => 223, series => "Harry Potter"'); Query books in the "Harry Potter" series: SELECT name, attributes->'author' AS authorFROM booksWHERE attributes->'series' = 'Harry Potter'; Result: The attributes for an individual book are displayed like this: SELECT * FROM books WHERE attributes->'series' = 'Harry Potter'; Conclusion PostgreSQL extensions significantly enhance database capabilities, enabling efficient handling of time-series data, encryption, indexing, key-value storage, and performance analysis. We went over a few popular tools like timescaledb, pgcrypto, and hstore; however, in reality, PostgreSQL supports many more extensions, offering solutions for a variety of use cases.

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.