Press Ctrl/Cmd + P to print
or save as PDF

Linux VPS Server Simple Tips Regarding Basic PostgreSQL Setup

PostgreSQL is an open-source relational database management system used by multiple platforms and VPS. For relational queries, PostgreSQL uses SQL, whereas, for non-relational queries, it uses JSON. PostgreSQL is a scalable and dependable database management system with advanced optimization features which would be a good addition to your Linux VPS server if you needed one. In this article, we will be guiding you on the basics of setting up PostgreSQL for your server.

Changing User Password

A user called “postgres” will be created after your PostgreSQL installation by default. To change the user’s password, run the following command in your terminal.

sudo passwd postgre

After running the command, you will be asked to input a new password and another time for the password verification. Alternatively, you may use the PostgreSQL prompt and change the password by switching to the default user using the following command.

su - postgres

By chance, if you had an error with it, you may set a valid shell on the user with the following command before switching to the user.

su --shell /bin/bash postgres

Then use the following command to change a new password.

psql -d template1 -c "ALTER USER postgres WITH PASSWORD '<password>';"

Note to change “<password>” in the command above with a strong and complex password of your own choice.

PostgreSQL Client Shell Simple Syntax

To switch to the PostgreSQL client shell, use the following command.

psql postgres

In this client shell, there are many available commands such as \h or \q to quit a certain environment.

The createdb command allows users to create new databases. For example, if we want to create a new database using the postgres user, here is how the command will look like.

create <database_name>

Replace the “<database_name>” in the command above with a name of your own choice for the database created. To create a new role, you may use the createuser command such as the command below.

createuser <user_name> -pwprompt

Replace “<user_name>” with a name for the new user. After running this command, you will be prompted to set a password for the new user.

To assign ownership of a newly created database to a user, here is the command you can run to do so.

createdb <database_name> -0 <user_name>

To connect to a database, use the following command.

psql <database_name>

To connect to a database with a specific user, you may use the command below.

psql <database_name> -U <user_name>

When using this command, you will need to enter the password for the user when prompted to.

To view all existing databases, use the \l or \list commands. As for viewing the database connected, use the \c command. To get more information regarding the connections, you may use the \conninfo command.

To delete a database, you may use the dropdb command such as the following command.

dropdb <database_name>

Double-check on the database before deleting them as they will not be retrievable after deletion.

You may use the \dt command to list all tables and \du command to list all users. PostgreSQL has a lot of similar functions as other database systems. Please look through the official documentation to learn more about PostgreSQL