1. How to Install Postgresql?
In Debian Linux, we can install postgresql easily with a line. But be sure your package manager is using a local mirror, otherwise you will wait very long time for downloading.
$ sudo apt install postgresql
2. Check After Installation
If everything in last step is all right, we are ready to use postgresql. However, we can have a check on it.
$ systemctl status postgresql
We should see “active”. It means postgresql database service is good and waiting for use.
Database is not only a static application but more like a service keep living underhood.
3. Using psql
psql is a terminal application shipped with postgresql installation. We can use it directly.
First we switch user to postgres. This postgres user is created by installation.
The reason we switch user is that psql requires to be run by user postgres only.
$ sudo su – postgres
$ psql
Now we can use psql. We can see our terminal is showing “postgres=#” as prompt.
Use this command to list all database:
postgres=# \l
Use this command to list all tables:
postgres=# \d
Use this command to list all users:
postgres=# \du
Use this command to quit psql:
postgres =# \q
We can actually stop here and use psql as an application to write any SQL lines and interactive with our databse. Besides, the ourput is decent well formatted.
Use this command to quit user postgres and go back to our original user.
$ exit
4. Install GUI Tool pgadmin4
We may need a GUI tool to intercative with databse as well. Who don’t like GUI after all.
There is a guide on official webpage: https://www.pgadmin.org/download/pgadmin-4-apt/
Following this guide you can easily add new source to package manager and install pgadmin4 with few lines.
However, this source is not a local mirror and it is very slow.
Another option is using python pip and pip’s advantage in this case is it can change to local mirror so it is faster.
First, we will install pip. Pip is a package manager just like apt or pacman, but it’s from python ecosystem.
$ sudo apt install python3-pip
Second, we will switch pip source to a local mirror:
$ pip3 config set global.index-url https://pypi.tuna.tsinghua.edu.cn/simple
Third, we will download pgadmin4’s package for pip to install. We can download it on it’s official webpage: https://www.pgadmin.org/download/pgadmin-4-python/
Forth, we will use pip and .whl package to install pgadmin4.
$ cd Downloads/
$ pip3 install pgadmin4-5.7-py3-none-any.whl
Maybe your .whl file will have a different name. That is not important.
During the installation, it will further download some new files as well. Luckly we have already changed the pip source to a local mirror.
After the installation, it will tell you a path where pgadmin4 is stored. In my case, it is ~/.local/bin/pgadmin4.
Fifth, run pgadmin4.
$ python3 ~/.local/bin/pgadmin4
It will show some errors because some folders don’t exist.
In my case, it said /var/lib/pgadmin and /var/log/pgadmin don’t exist. We can fix it easily.
$ sudo mkdir /var/lib/pgadmin
$ sudo chown yourname /var/lib/pgadmin
$ sudo mkdir /var/log/pgadmin
$ sudo chown yourname /var/log/pgadmin
This yourname variable is your Linux user name.
Now we can run pgadmin4
$ python3 ~/.local/bin/pgadmin4
First time launch it will require you set a email as login name and your password. This login name and password is only for pgadmin4 login.
After that we can use http://127.0.0.1:5050 in browser to login pgadmin4.
5. Configure pgadmin4 to connect to database
Now we are in http://127.0.0.1:5050 and it has a GUI window, but we are not connect to database yet.
One confusing point is, you cannot use user name postgres to connect to databse. We will need a new user name.
So firstly we need to create another user in psql.
Remember how to use psql?
$ sudo su - postgres
$ psql
$ \du
$ create user yourname superuser password ‘yourpassword’
Now we go back to pgadmin4 application page. Click “Add new server”.
In the tab General, we will fill a server name. Like local_db is fine for me.
In the tab Connect, we will write host name as localhost. In the same tab we will fill database user name and password we just created in psql. Notice this is not the name and password when you login pgadmin4 application.
After this is done, we will see we are connecting to local database server as we need.
Another GUI tool option is phppgadmin. It is already in apt list so is more easily to install.
$ sudo apt install phppgadmin
It doesn’t have SQL grammar color so I don’t go into it very often.