Knowledgebase

PostgreSQL is a powerful, open-source relational database management system known for its robustness, flexibility, and advanced features. Installing PostgreSQL on a VPS, such as one hosted with AnonVM, allows you to set up a reliable database solution for various applications. In this guide, we’ll walk through the steps of installing PostgreSQL on a Linux-based VPS.

System Requirements

Before we begin, ensure your VPS meets the following prerequisites:

• Operating System: Ubuntu 20.04+, Debian 10/11, or CentOS 7+
• User Privileges: Root or sudo access

Step 1: Update Your Server

Start by updating the package list and upgrading any existing packages:

For CentOS, use:

Step 2: Install PostgreSQL

For Debian/Ubuntu Users:

1. Install PostgreSQL from the default repositories:

    

   sudo apt install postgresql postgresql-contrib -y

2. Start and Enable PostgreSQL to run on system startup:

    

   sudo systemctl start postgresql sudo systemctl enable postgresql

For CentOS Users:

1. Add the PostgreSQL repository:

    

   sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2. Install PostgreSQL:

    

   sudo yum install postgresql13-server postgresql13 -y

3. Initialize the Database:

    

   sudo /usr/pgsql-13/bin/postgresql-13-setup initdb

4. Start and Enable PostgreSQL:

    

   sudo systemctl start postgresql-13 sudo systemctl enable postgresql-13

Step 3: Verify PostgreSQL Installation

Once installed, check the PostgreSQL version to confirm the installation:

Step 4: Basic PostgreSQL Configuration

Set the PostgreSQL Password:

1. Switch to the PostgreSQL User:

    

   sudo -i -u postgres

2. Open the PostgreSQL Command Line:

    

   psql

3. Set a Password for the PostgreSQL User:

   sql

    

   ALTER USER postgres WITH PASSWORD 'your_secure_password';

4. Exit the PostgreSQL prompt:

   sql

    

   \q

5. Exit the PostgreSQL user session:

    

   exit

Step 5: Configure Remote Access (Optional)

If you plan to access the database remotely, you’ll need to allow external connections.

1. Edit PostgreSQL Config File:

   Open the postgresql.conf file:

    

   sudo nano /etc/postgresql/13/main/postgresql.conf # Ubuntu/Debian sudo nano /var/lib/pgsql/13/data/postgresql.conf # CentOS

   Locate the listen_addresses line and change it to allow remote connections:

   plaintext

    

   listen_addresses = '*'

2. Configure Client Authentication:

   Edit the pg_hba.conf file to add IP addresses that can connect to your PostgreSQL server:

    

   sudo nano /etc/postgresql/13/main/pg_hba.conf # Ubuntu/Debian sudo nano /var/lib/pgsql/13/data/pg_hba.conf # CentOS

   Add a line at the end to allow access:

   plaintext

    

   host all all 0.0.0.0/0 md5

   Note: Replace 0.0.0.0/0 with a specific IP range for better security.

3. Restart PostgreSQL:

    

   sudo systemctl restart postgresql

Step 6: Create a New Database and User

1. Switch to the PostgreSQL User:

    

   sudo -i -u postgres

2. Access the PostgreSQL Shell:

    

   psql

3. Create a New Database:

   sql

    

   CREATE DATABASE your_database_name;

4. Create a New User and Set Password:

   sql

    

   CREATE USER your_username WITH ENCRYPTED PASSWORD 'your_password';

5. Grant Privileges to the User:

   sql

    

   GRANT ALL PRIVILEGES ON DATABASE your_database_name TO your_username;

6. Exit the PostgreSQL Shell:

   sql

    

   \q

7. Exit PostgreSQL User Session:

    

   exit

Step 7: Test the PostgreSQL Connection

To verify that PostgreSQL is working correctly, connect to the database using psql:

You’ll be prompted to enter the password you set for the user. If successful, you should be able to execute SQL commands.

Step 8: Secure PostgreSQL (Optional)

To enhance security, consider the following:

1. Change Default Port: In the postgresql.conf file, change the default port (5432) to a non-standard one if security is a concern.

2. Enable SSL: Configuring SSL can secure connections to PostgreSQL, especially for remote access.

3. Restrict Remote Connections: Avoid 0.0.0.0/0 in pg_hba.conf for IP ranges; instead, specify only trusted IP addresses.

4. Regular Backups: Schedule regular backups of your databases using pg_dump or other backup tools.

Conclusion

You have now successfully installed and configured PostgreSQL on your AnonVM VPS! With PostgreSQL up and running, you can now manage and query your databases effectively. Remember to regularly update PostgreSQL and follow best practices for security, especially if your database is accessible from the internet.