Postgresql development environment setup - Arch Linux

Posted by Suyash Singh

on October 21, 2022

Arch LinuxLinuxPostgresDockerSQL

Here’s how to setup a development environment for postgresql in Arch linux:

Step 1 — Install postgresql and register it with systemd

$ sudo pacman -S postgresql
$ sudo systemctl enable postgresql
$ sudo systemctl start postgresql

Step 2 — Initial DB setup

The above installation, using pacman hooks setups a new user postgres. The default directory for postgresql is /var/lib/postgres. Make sure the data directory inside this location is owned by postgres user. After ensuring that, initialize the DB:

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

Step 3 — Go to a psql shell

$ psql

Step 4 — Set password for user=postgres

postgres=# ALTER USER postgres WITH ENCRYPTED PASSWORD '<NEW_PASSWORD>';

Step 5 — Create new user interactively

Create a user we’d need. For instance a non superuser with RW access to the database.

[postgres]$ createuser --interactive

Step 6 — Now we can create our DBs / Connect using pgAdmin web interface

$ vi docker-pgadmin.yml

version: '3.8'
services:
  pgadmin:
    container_name: pgadmin4_container
    network_mode: host
    image: dpage/pgadmin4
    restart: always
    environment:
      PGADMIN_DEFAULT_EMAIL: admin@admin.com
      PGADMIN_DEFAULT_PASSWORD: root
    ports:
      - "5050:80"
 

Notice the network_mode: host setting. This would allow us to connect to our postgresql from the pgAdmin container.

Step 7 — Access pgAdmin / Setup pgAdmin users on localhost:80

The network_mode: host setting above bridges the container network with the host. Therefore, pgAdmin is now accessibly on port 80 of the host.

Step 8 — Securing postgres

Usually for our dev environment we don’t really have to have a very tight security for postgres. However, if we feel the need, we can change around the postgres policies accordingly. The configurations for it is beyond the scope of this post. Read more about securing postgres here.

Additionally, make sure to secure the pgAdmin as well, if need be, by changing passwords and setting up 2FAs.