DEV Community

Cover image for Installing Postgres on a Development Machine
Talles L
Talles L

Posted on

2

Installing Postgres on a Development Machine

Sure, it's quicker and easier to run as a container, I know. But some people sometimes just want to have it the old way.

I'm on Pop!_OS 22.04 by the way.

Adding the APT repository and Installing It

At time of writing, apt show postgresql tells me that version 14 will be installed while I can see version 17 on postresql.org homepage.

Thankfully there are some clear instructions on the website how to set up postgresql.org APT repo on your system. I went the lazy route:

$ sudo apt install -y postgresql-common

$ sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
Enter fullscreen mode Exit fullscreen mode

After that apt show postgresql was giving me version 17, great. Proceeding to installation:

$ sudo apt install postgresql
Enter fullscreen mode Exit fullscreen mode

The Systemd Service

After installation was finished I got postgresql up and running as service, without any configuration of my own:

$ systemctl status postgresql.service 
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Fri 2024-11-29 23:03:31 -03; 1min 2s ago
   Main PID: 17315 (code=exited, status=0/SUCCESS)
        CPU: 1ms

Nov 29 23:03:31 pop-os systemd[1]: Starting PostgreSQL RDBMS...
Nov 29 23:03:31 pop-os systemd[1]: Finished PostgreSQL RDBMS.

Enter fullscreen mode Exit fullscreen mode

Out of curiosity, let's check the .service file:

$ cat /lib/systemd/system/postgresql.service
# postgresql.service is the meta unit for managing all PostgreSQL clusters on
# the system at once. Conceptually, this unit is more like a systemd target,
# but we are using a service since targets cannot be reloaded.
#
# The unit actually managing PostgreSQL clusters is postgresql@.service,
# instantiated as postgresql@15-main.service for individual clusters.

[Unit]
Description=PostgreSQL RDBMS

[Service]
Type=oneshot
ExecStart=/bin/true
ExecReload=/bin/true
RemainAfterExit=on

[Install]
WantedBy=multi-user.target
Enter fullscreen mode Exit fullscreen mode

Wait a second, that's just a dummy service pointing to /bin/true.

Fiddling a bit I figure it out that there was another service, postgresql@17-main.service:

$ systemctl status postgresql@17-main.service 
● postgresql@17-main.service - PostgreSQL Cluster 17-main
     Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled-runtime; vendor preset: enabled)
     Active: active (running) since Fri 2024-11-29 23:03:35 -03; 4min 20s ago
    Process: 18149 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 17-main start (code=exited, status=0/SUCCESS)
   Main PID: 18154 (postgres)
      Tasks: 6 (limit: 38324)
     Memory: 21.3M
        CPU: 267ms
     CGroup: /system.slice/system-postgresql.slice/postgresql@17-main.service
             ├─18154 /usr/lib/postgresql/17/bin/postgres -D /var/lib/postgresql/17/main -c config_file=/etc/postgresql/17/main/postgresql.conf
             ├─18155 "postgres: 17/main: checkpointer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "">
             ├─18156 "postgres: 17/main: background writer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" >
             ├─18158 "postgres: 17/main: walwriter " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "">
             ├─18159 "postgres: 17/main: autovacuum launcher " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ">
             └─18160 "postgres: 17/main: logical replication launcher " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ">

Nov 29 23:03:32 pop-os systemd[1]: Starting PostgreSQL Cluster 17-main...
Nov 29 23:03:35 pop-os systemd[1]: Started PostgreSQL Cluster 17-main.
Enter fullscreen mode Exit fullscreen mode

Checking Connectivity with pg_ready

There's a lovely utility for checking connectivity with Postgres:

$ pg_isready 
/var/run/postgresql:5432 - accepting connections
Enter fullscreen mode Exit fullscreen mode

Enabling Trust Authentication

This is a development machine and Postgres got us covered with what they call trust authentication. No need to remember usernames and password? I'm in.

To enable it, will change the following file:

$ sudo vim /etc/postgresql/17/main/pg_hba.conf
Enter fullscreen mode Exit fullscreen mode

Locate the following line:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

Enter fullscreen mode Exit fullscreen mode

Replace "peer" for "trust":

# Database administrative login by Unix domain socket
local   all             postgres                                trust

Enter fullscreen mode Exit fullscreen mode

In the same file, locate the following line:

# "local" is for Unix domain socket connections only
local   all             all                                     peer
Enter fullscreen mode Exit fullscreen mode

Again, replace "peer" for "trust":

# "local" is for Unix domain socket connections only
local   all             all                                     trust
Enter fullscreen mode Exit fullscreen mode

Restart the service:

$ sudo systemctl restart postgresql@17-main.service
Enter fullscreen mode Exit fullscreen mode

Logging into the database

Let's get into the database with the "postgres" user:

$ psql --username=postgres
psql (17.2 (Ubuntu 17.2-1.pgdg22.04+1))
Type "help" for help.

postgres=# 
Enter fullscreen mode Exit fullscreen mode

Run the following:

postgres=# CREATE ROLE <your-username> WITH LOGIN SUPERUSER;

postgres=# CREATE DATABASE <your-username> OWNER <your-username>;
Enter fullscreen mode Exit fullscreen mode

Ctrl+D back to your shell. You should be able to get into the database by just calling psql with no arguments now:

$ psql
psql (17.2 (Ubuntu 17.2-1.pgdg22.04+1))
Type "help" for help.

talles=# 
Enter fullscreen mode Exit fullscreen mode

Installing pgAdmin

We have to add a new repository:

$ curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg

$ sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'
Enter fullscreen mode Exit fullscreen mode

Then:

$ sudo apt install pgadmin4-desktop
Enter fullscreen mode Exit fullscreen mode

Installation is done. Open up pgAdmin and head to the "register server" dialog. To use local authentication, put /var/run/postgresql in the hostname field:

(credits to this Stack Overflow answer for this trick)

You should be in :)

Do your career a big favor. Join DEV. (The website you're on right now)

It takes one minute, it's free, and is worth it for your career.

Get started

Community matters

Top comments (1)

Collapse
 
mati_brian_0e103a6fd3f89d profile image
mati brian

thanks..ctrl +c && ctrl + v

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay