How to Install PostgreSQL on Debian

Install WordPress on AlexHost VPS with cPanel and Softaculous

Why install WordPress on AlexHost? WordPress powers over 40% of websites, offering unmatched flexibility for blogs, e-commerce, and portfolios. AlexHost’s VPS with NVMe storage, LiteSpeed, and DDoS protection delivers blazing-fast performance and robust security. Using cPanel and Softaculous, you can install WordPress in minutes. This guide walks you through the installation process and key configurations for a secure, optimized setup on AlexHost.

Step 1: Update Your System

Before installing PostgreSQL, it’s always a good idea to ensure that your system’s package list is up-to-date.

1. Open a terminal and run the following commands to update the package list:
   sudo apt update

   sudo apt upgrade

This ensures that all the packages on your Debian system are the latest versions.

Step 2: Install PostgreSQL

PostgreSQL is included in the official Debian repositories, which makes installation simple.

1. Install PostgreSQL by running the following command:
   sudo apt install postgresql postgresql-contrib

• postgresql: This installs the PostgreSQL database server.
• postgresql-contrib: This package includes additional tools and utilities that are commonly used with PostgreSQL.

Step 3: Verify the Installation

After the installation completes, PostgreSQL should be running on your system.

1. Check the PostgreSQL service status:
   sudo systemctl status postgresql

You should see an output indicating that PostgreSQL is active and running. If it’s not running, you can start the service using:

Additionally, you can enable PostgreSQL to start automatically at boot:

Step 4: Basic PostgreSQL Configuration

PostgreSQL runs under a default user named postgres, which is a superuser with administrative privileges. To configure PostgreSQL, you can switch to this user and start interacting with the database.

1. Switch to the PostgreSQL User

1. Use the following command to switch to the postgres user:
   sudo -i -u postgres
2. Once logged in as the postgres user, you can access the PostgreSQL prompt by typing:
   psql

This will open the PostgreSQL interactive terminal where you can run queries, create users, and manage databases.

2. Create a New PostgreSQL User

PostgreSQL supports role-based authentication. You can create a new user (or “role”) to manage your databases.

1. To create a new user, enter the following command at the psql prompt:
   CREATE USER your_username WITH PASSWORD 'your_password';

Replace your_username with the desired username and your_password with a strong password.

2. To give this new user superuser privileges (optional), run:
   ALTER USER your_username WITH SUPERUSER;
3. Exit the psql prompt by typing:
   \q

3. Create a New Database

After creating a user, you can create a new database for that user.

1. To create a new database, use the following command:
   createdb your_database_name

Replace your_database_name with the name of your new database.

2. To grant ownership of the database to the newly created user:
   ALTER DATABASE your_database_name OWNER TO your_username;

Step 5: Configure Remote Access (Optional)

By default, PostgreSQL only accepts connections from localhost (127.0.0.1), which means it’s not accessible remotely. If you want to allow remote connections, follow these steps.

1. Modify PostgreSQL Configuration

1. Open the PostgreSQL configuration file:
   sudo nano /etc/postgresql/14/main/postgresql.conf

   (Note: Adjust the version number 14 to match your installed PostgreSQL version.)

2. Look for the following line:
   #listen_addresses = 'localhost'
3. Uncomment the line and change it to:
   listen_addresses = '*'

   This will allow PostgreSQL to listen on all network interfaces. You can specify an IP address here if you want to restrict access to a specific address.

4. Save the file and exit.

2. Modify Client Authentication

Next, modify the pg_hba.conf file to configure which IP addresses are allowed to connect.

1. Open the pg_hba.conf file:
   sudo nano /etc/postgresql/14/main/pg_hba.conf
2. Add the following line at the end of the file:
   host all all 0.0.0.0/0 md5

   This allows all IP addresses (0.0.0.0/0) to connect to the database using password authentication (md5). For more restrictive access, you can specify a range of IP addresses instead of 0.0.0.0/0.

3. Save the file and exit.

3. Restart PostgreSQL

After making changes to the configuration, restart PostgreSQL to apply the new settings:

Step 6: Connect to PostgreSQL Remotely

If you enabled remote access, you can now connect to your PostgreSQL database from another machine. You will need to install a PostgreSQL client on the remote machine if it’s not already installed.

1. Install the PostgreSQL client:
   sudo apt install postgresql-client
2. Connect to your PostgreSQL server:
   psql -h your_server_ip -U your_username -d your_database_name

Replace your_server_ip with the IP address of the PostgreSQL server, your_username with the PostgreSQL user you created, and your_database_name with the name of the database.

Step 7: Managing PostgreSQL Databases

Once PostgreSQL is installed, you can manage databases using both the PostgreSQL shell (psql) and command-line utilities.

1. Listing Databases

To list all databases on your PostgreSQL server, use the following command inside the psql prompt:

2. Switching Databases

To switch between databases, you can use:

This connects you to the specified database.

3. Listing Tables

To list all tables in the current database:

4. Backup and Restore Databases

To back up a PostgreSQL database, use the pg_dump command:

To restore a database from a backup:

Conclusion: Launch and Scale Your WordPress Site with AlexHost VPS

Installing WordPress on AlexHost’s VPS using cPanel and Softaculous is a seamless process that empowers users of all skill levels to create a professional website quickly and efficiently. By following the detailed steps outlined in this guide, you can configure essential settings such as the WordPress version, Multisite mode, and cron jobs, while ensuring your site remains secure and optimized.