Setup local PostgreSQL and pgAdmin on ArchLinux

postgresql_arch_tutorial.md

Working with local PostgreSQL and pgAdmin on ArchLinux

Daily Usage

Start postgreSQL

$ sudo systemctl start postgresql

Stop postgreSQL

$ sudo systemctl stop postgresql

Starting pgAdmin

$ pgadmin4

---

Initital Setup

Install packages

$ sudo pacman -S postgresql pgadmin4

Setup postgresql

PostgreSQL automatically creates a new system user called postgres. Most of the PostgreSQL commands must be executed by this user. Commands that should be run as the postgres user are prefixed by [postgres]$ in this article. You can switch to this user with:

$ sudo -iu postgres

After installing the package you have to init the database cluster (execute as postgres user):

[postgres]$ initdb -D /var/lib/postgres/data

You can now exit to your local user by:

$ exit

Now start the postgreSQL Database with:

$ sudo systemctl start postgresql

If you want postgreSQL to start automatically on system boot also type:

$ sudo systemctl enable postgresql

Add a new database user (execute as postgres):

$ sudo -iu postgres  
[postgres]$ createuser --interactive -P

The -P flag creates a user with a password

Usefull Links:

https://wiki.archlinux.org/index.php/PostgreSQL

---

Using pgAdmin

Run pgAdmin by selecting the apllication from your launcher or type:

$ pgadmin4

Make sure the PostgreSQL Server is running. You can see the server's log messages (including the port number, which will be neccessary later on) in systemd's log. Access the log with:

$ journalctl -e

Exit the journal by pressing q

Add your PostgreSQL server to pgAdmin

Now add the PostgreSQL Server in the pgAdmin Control Panel by clicking on "Add New Server".

Give your server configuration a name.

Under the "Connection" Tab set the following properties

• Use 127.0.0.1 (localhost) as Hostname
• The port, your server is listening on. (By default this is 5432)
• Use postgres as Maintenance database
• The username and password of the PostgeSQL user you created so far

If this is a lucky day everything should work now!

@AkumuKernel do you have postgres installed ..it seems your connection refused could stem from the fact that your postgres server might not be running on your localhost port 5432

@AkumuKernel do you have postgres installed ..it seems your connection refused could stem from the fact that your postgres server might not be running on your localhost port 5432

How to change the port?

After typing pgadmin4 on the terminal throwing this issue

ERROR : Failed to create the directory /var/log/pgadmin:
[Errno 13] Permission denied: '/var/log/pgadmin'
HINT : Create the directory /var/log/pgadmin, ensure it is writeable by
'............', and try again, or, create a config_local.py file
and override the LOG_FILE setting per
https://www.pgadmin.org/docs/pgadmin4/6.11/config_py.html

use sudo

Where is the pgadmin now? There is no nor in pacman, neither in aur.