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!

Hey there,
thanks so much for this guide. It probably saved me hours of research. A few things i ran into that one should mention:

1.

If you execute the command: [postgres]$ initdb -D /var/lib/postgres/data and run into the following error:

error while loading shared libraries: libicui18n.so.65: cannot open shared object file: NO such file or directory. initdb: Error: The program "postgres" is required by initdb, but could not be found in the same directory "/user/bin/initdb".

Make sure your System is up to date. The error might disappear magically.

2.

Make sure to open a new Terminal after executing $ pgadmin4 , since it will block the current Terminal.

i can access psql fine via CLI but pgadmin isnt working, any ideas?

/home/hadi/.config/gtk-2.0/gtkrc-2.0:4: Unable to find include file: "~/.gtkrc-2.0.mine"
Semaphore name: "pgadmin4-hadi-a59283b86ae8ae85d20fdf770d799dd7-sema"
Shared memory segment name: "pgadmin4-hadi-a59283b86ae8ae85d20fdf770d799dd7-shmem"
Python path: "/usr/lib/python3.9:/usr/lib/python3.9/lib-dynload:/usr/lib/python3.9/site-packages"
Python Home: "/usr/lib/python3.9"
Webapp path: "/usr/lib/pgadmin4/web/pgAdmin4.py"
"An error occurred initialising the pgAdmin 4 server:\n\nFailed to launch the application server, server thread exiting."

Hey @rickithadi
I don't know a solution for your individual problem.
If you're working on arch linux you could try to do a full system upgrade . This is often a "magic solution" for problems between some packages on arch.

If i understand you correctly, pgAdmin4 isn't working at all. So you could try to ask for your problem there: https://www.pgadmin.org/support/

This was helpful but the solution doesnt work for pgadmin for some unknown reasons, the solution to that is to uninstall the pgadmin

sudo pacman -R pgadmin4

And create and activate a python virtual environment.When done type

pip install pgadmin4

Then Run Pgadmin4 by typing

pgadmin4

Then it would ask for email and password to access your dashboard, when done , would redirect you to the browser webpage.

@oyewunmio Thanks for your comment and for sharing your solution 🎉

Do you want to explain what exactly does not work when using the default archlinux pgadmin4 package via pacman? And what you mean by create and activate a python virtual environment ?

If you do so I could add your experiences to my guide...

@oyewunmio Thanks for your comment and for sharing your solution tada

Do you want to explain what exactly does not work when using the default archlinux pgadmin4 package via pacman? And what you mean by create and activate a python virtual environment ?

If you do so I could add your experiences to my guide...

Apparently its not necessary to create a virtual environment but I'd say its a good practice. If you don't know what a virtual environment is, well this is the environment that is completely isolated and does not affect the your main python environment whenever you install a new package.

QCoreApplication::applicationFilePath: Please instantiate the QApplication object first
QCoreApplication::applicationFilePath: Please instantiate the QApplication object first
QApplication: invalid style override 'adwaita' passed, ignoring it.
Available styles: Windows, Fusion
Semaphore name: "pgadmin4-akumu-a59283b86ae8ae85d20fdf770d799dd7-sema"
Shared memory segment name: "pgadmin4-akumu-a59283b86ae8ae85d20fdf770d799dd7-shmem"
Python path: "/usr/lib/python3.10:/usr/lib/python3.10/lib-dynload:/usr/lib/python3.10/site-packages"
Python Home: "/usr/lib/python3.10"
Webapp path: "/usr/lib/pgadmin4/web/pgAdmin4.py"
Failed to connect to the server: "Connection refused" - request URL: "http://127.0.0.1:39401/misc/ping?key=51cd860c-dbe9-4204-b128-c3afab9b2869" .

@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.

pgadmin is gone from arch repository :(

pgadmin is gone from arch repository :(

have you found an alternative way for it?

pgadmin is gone from arch repository :(

have you found an alternative way for it?

I haven't tried it, but there's a web based alternative on AUR called phpPgAdmin: https://wiki.archlinux.org/title/PhpPgAdmin

I haven't tried it, but there's a web based alternative on AUR called phpPgAdmin: https://wiki.archlinux.org/title/PhpPgAdmin

i used their python package here, yes it is more of a hassle than an AUR package but at least you won't risk it breaking or touching anything, works pretty well too

THANKS