Using PostgreSQL on VPS: Installation and Configuration

psql

\password postgres

CREATE DATABASE my_database;

CREATE USER my_user WITH PASSWORD 'my_password';

GRANT ALL PRIVILEGES ON DATABASE my_database TO my_user;

8. Check PostgreSQL Status:

To ensure that PostgreSQL is running and your changes have been applied successfully, check the service status:

sudo systemctl status postgresql  # For Ubuntu/Debian
sudo systemctl status postgresql-13  # For CentOS/RHEL

You should see output indicating that PostgreSQL is active and running.

9. Exit PostgreSQL Command Line:

After completing the initial configuration, you can exit the PostgreSQL command-line interface by typing:

\q

Creating and Managing PostgreSQL Users

In PostgreSQL, users are essential for managing access and permissions to databases. You can create, modify, and delete users as needed. Here’s a step-by-step guide for creating and managing PostgreSQL users.

1. Create a New User:

To create a new user, you use the CREATE USER command. It’s important to assign a secure password when creating a user.

CREATE USER username WITH PASSWORD 'password';

Replace username with the desired username and password with a strong password for the user.

2. Granting Privileges to Users:

After creating a user, you need to grant them appropriate permissions to interact with the database. You can grant the user access to specific databases or allow them specific operations.

• Grant All Privileges on a Database: This command gives the user full access to a specific database.

  GRANT ALL PRIVILEGES ON DATABASE my_database TO username;

  Replace my_database with the name of the database and username with the user’s name

• Grant Specific Privileges: You can grant more specific permissions, such as the ability to only select data from a table:

  GRANT SELECT ON my_table TO username;

  Replace my_table with the table name and username with the user.

3. Modify an Existing User:

You can alter an existing user’s properties, such as their password, login privileges, or membership in roles.

• Change a User’s Password: To change the password of an existing user:

  ALTER USER username WITH PASSWORD 'new_password';

  Grant Login Privileges: You can allow or disallow a user’s ability to log in by altering their properties. For example, to disable login for a user:

  ALTER USER username NOLOGIN;

  To allow the user to log in again:

  ALTER USER username LOGIN;

4. Managing User Roles:

In PostgreSQL, roles can be used to group users together. You can create roles and assign them to users to manage their permissions more easily.

• Create a New Role: You can create roles that group users with similar permissions.

  CREATE ROLE role_name;

  Grant Role to a User: Once a role is created, you can grant it to a user, which gives the user the permissions associated with that role.

  GRANT role_name TO username;

• Revoke a Role from a User: To revoke a role from a user, use the following command:

  REVOKE role_name FROM username;

5. Listing Users and Roles:

You can list all users and roles in PostgreSQL by querying the system catalog.

\du

This command will display a list of all roles and their associated attributes.

6. Delete a User:

If you no longer need a user, you can remove them from the PostgreSQL system.

DROP USER username;

Replace username with the name of the user you want to delete. Make sure that the user does not own any objects in the database or transfer ownership before deleting the user.

7. Reassigning Objects Owned by a User:

If you need to delete a user but that user owns objects in the database (like tables or schemas), you must first reassign those objects to another user:

REASSIGN OWNED BY username TO new_owner;

Replace username with the user whose objects you want to reassign and new_owner with the user who will take ownership of the objects.

Setting Up Backups and Restoring PostgreSQL Databases

Backing up your PostgreSQL database is critical to ensure that your data is safe in case of a failure or corruption. Restoring backups allows you to recover the database to a previous state. Here’s how to set up backups and restore PostgreSQL databases.

1. Using pg_dump to Backup a PostgreSQL Database:

The pg_dump utility is used to create backups of a PostgreSQL database. It can back up a single database or all databases, and it supports multiple formats, such as plain SQL, custom, or directory format.

• Backing Up a Single Database:To create a backup of a single database, use the following command:

  pg_dump my_database > my_database_backup.sql

  Replace my_database with the name of your database. This will create a plain SQL backup file (my_database_backup.sql) that contains the SQL statements necessary to recreate the database.

• Backing Up All Databases:If you want to back up all databases on the PostgreSQL server, use the pg_dumpall command:

  pg_dumpall > all_databases_backup.sql

  This command will create a backup of all databases in a plain SQL format.

• Backing Up in Custom Format:To back up in a custom format (which allows for more flexibility when restoring), use the -Fc flag:

  pg_dump -Fc my_database > my_database_backup.dump

  This command creates a compressed backup file that can be restored using the pg_restore utility.

2. Automating Backups with Cron:

To automate backups on a regular schedule, you can use cron jobs. This allows you to set up periodic backups without manual intervention.

• Edit the Crontab File:To edit the crontab for the PostgreSQL user, run the following command:

  crontab -e

• Set Up a Backup Schedule:Add a cron job to schedule backups. For example, to back up a database every day at midnight, add the following line to the crontab:

  0 0 * * * pg_dump my_database > /path/to/backup/my_database_backup_$(date +\%F).sql

  This command will create a backup of my_database at midnight every day, with the filename including the current date (YYYY-MM-DD format).

3. Restoring a PostgreSQL Database from a Backup:

Restoring from a backup is just as simple as creating one. The method you use depends on the format of the backup.

• Restoring from a Plain SQL Backup:If you have a plain SQL backup, you can restore it using the psql command:

  psql my_database < my_database_backup.sql

  Replace my_database with the name of the database you want to restore to, and my_database_backup.sql with the path to your backup file.

• Restoring from a Custom Format Backup:If you backed up using the custom format (e.g., with pg_dump -Fc), you can restore using pg_restore:

  pg_restore -d my_database my_database_backup.dump

  Replace my_database with the name of the database to restore to and my_database_backup.dump with your custom-format backup file.

• Restoring All Databases from a Backup:If you used pg_dumpall to back up all databases, you can restore all databases with the following command:

  psql -f all_databases_backup.sql postgres

  This will restore all databases from the backup file all_databases_backup.sql.
  

4. Verifying Backups:

It is important to verify that your backups are valid and can be restored. You can verify a backup by restoring it to a test database and checking its integrity.

• Restore to a Test Database:Create a new test database and restore the backup to this database:

  createdb test_database
  psql test_database < my_database_backup.sql

• Verify the Restored Data:After restoring, check the data to ensure it is complete and consistent with the original database.

5. Backing Up Configuration Files:

In addition to the database data itself, it is also important to back up PostgreSQL’s configuration files, which include postgresql.conf and pg_hba.conf. These files are typically located in the data directory (/var/lib/postgresql/12/main for Ubuntu or /var/lib/pgsql/12/data for CentOS/RHEL).

You can back them up using the following command:

cp /path/to/postgresql.conf /path/to/backup/postgresql.conf.bak
cp /path/to/pg_hba.conf /path/to/backup/pg_hba.conf.bak