Postgres install, login, show DB’s & more

This is going to be another quick and dirty article. I am going to show you how to install Postgresql, login to it and list all databases, delete a database, change users password and how to log out of postgres.

I expect that you already have a running Ubuntu Server installation but if you do not please read here to get up to speed. Postgresql works a little bit different than MySQL or Maria DB and I am going to show you how you can login, change password and how to show a list of databases in Postgresql.

Installing postgresql

In Ubuntu and all other Debian bases systems it is super easy to install application and services. The following command install the postgres SQL database system.

sudo apt install postgresql

The installation process should finish up with something like the following:

Postgresql installation process
Postgresql installation process

Congratulations you installed postgresql database system

Log in to Postgresql

Start up your command line interface and type the following command:

sudo -u postgres psql postgres

This should return something similar to the following:

psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1))
Type “help” for help.

postgres=#

If you try to run the psql postgres command without switching the console to the postgres user you will see something similar to the following:

psql: error: FATAL: role “your_username” does not exist

This error occurs due to the fact that your username is not a permitted user in the postgres database.

Lets break that command down. The fist part “sudo -u postgres” changes the login to the postgres user who can connect properly to postgresql, followed by the command “psql” which is actually the command line postgresql client and the the last “postgres” is the actual database that holds the postgres users and passwords and permission levels.

Change password

Now that you are logged into postgresql you can run the command to change the password for the postgres user:

\password postgres

The first part “\password” is the command that initiates the password change and the second part “postgres” is the username you like to change the password for. You should see something similar to the following:

postgres=# \password postgres
Enter new password:
Enter it again:
postgres=#

As with anything on Linux if you are not getting a statement back usually this means it is all good, If there is an error or problem you should see something like the following:

ERROR: role “postgre” does not exist

I mistyped the user postgres and the database could not find that user. This is just on example.

Create new database

Since you are already logged in lets create a new database. In order to do that run the following command:

CREATE DATABASE myappsdb;

In SQL the commands are usually typed in all upper case letter. Our database we are creating “myappsdb” is written usually in all lower case letters and each SQL command terminates with a semicolon.

Show a list of Databases

Now that we have created a new database lets see a list of all databases in this postgres install. Run the following command:

\l

You should see a list of databases similar to the following below:

List of Postgres Databases
List of Postgres Databases

Deleting Database

In order to delete a database in postgres you would need to run the following command:

DROP DATABASE myappsdb;

The command break down is similar to the one where we created a database. The commands are written in all upper case letters and the object in questions is written in lower case letters followed by a semicolon to terminate the command.

Quitting out of Postgres

Now that we learned how to log in to the database, changed password, created and deleted a database we need to know how to log out of the database system. Execute the following command:

\q

This terminates the current postgres psql session and you are back to your command prompt.

Conclusion

In this article I showed you how you properly login to Postgres database system with psql, changed a users password, created and deleted a database, list all existing databases and how to log off from the database system. If you like to read up more about Postgres read here. I hope you find this article useful and I am looking forward to your comments and suggestions for future articles. Thank you for reading

Ciao!