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.
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:
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.
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.
Now that you are logged into postgresql you can run the command to change the password for the postgres user:
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:
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:
You should see a list of databases similar to the following below:
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:
This terminates the current postgres psql session and you are back to your command prompt.
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