How to install and configure PostgreSQL on a VPS?

Adding a PostgreSQL to your Virtual Private Server allows you to organize and store your data. Here’s how you can do this on a Linux VPS:

1. Update your VPS
Ensure your package lists and the server is up-to-date by inputting the following commands:

\`\`\` sudo apt-get update sudo apt-get upgrade \`\`\`

1. Install PostgreSQL
Use the following command to install PostgreSQL:

\`\`\` sudo apt-get install postgresql postgresql-contrib \`\`\` During the installation, an operating system user named postgres is created. This user also owns the server process.

1. Switch to the postgres account
As the initial account, you need to log in and connect with PostgreSQL. Do this with the following command:

\`\`\` sudo -i -u postgres \`\`\`

1. Access a Postgres Session
Afterwards, you can access a Postgres session by using the command:

\`\`\` psql \`\`\`

1. Exit out of the PostgreSQL prompt by typing

\`\`\` \q \`\`\`

1. Create a New Role
First, create a new user by inputting `sudo adduser name_of_new_role`. You can switch over to the postgres account, and then create a new database user:

\`\`\` sudo -i -u postgres createuser —interactive \`\`\`

1. Create a New Database
Create a new Linux user first: `sudo adduser new_user`. Then, open up the PostgreSQL prompt: `sudo -u postgres createdb name_of_new_db`.

1. Open PostgreSQL and Set Up User Permissions
PostgreSQL will set up the necessary user permissions; type `psql` and then set a password for the new PostgreSQL user:

\`\`\` \password name_of_new\_user \`\`\`

1. Edit the Authentication Method
Change the authentication method from ident to md5 by altering the PostgreSQL configuration file, usually located at `/etc/postgresql/9.5/main/pg_hba.conf`. Use this command to open up the file:

\`\`\` sudo nano /etc/postgresql/9.5/main/pg\_hba.conf \`\`\` Find the following lines in the file: \`\`\` # Database administrative login by Unix domain socket local all postgres peer # TYPE DATABASE USER ADDRESS METHOD \`\`\` Change “peer” and “md5” to “trust” and save and close the file.

1. Restart PostgreSQL

\`\`\` sudo service postgresql restart \`\`\`

Congratulations, you have installed and configured PostgreSQL on your VPS.