5.4 KiB
Classes
| Classes | |
|---|---|
|
Postgresql
Postgresql is a popular open-source relational database, and is widely available on Linux.
Note: A graphical interface for postgresql is something that will need to be installed separately, the only way to interact with the database that is installed by default is the psql command. To install the pgAdmin GUI program see pgAdmin below.
Install
All linux distributions package postgresql and you can just install the relevant package. Some common examples are below:
Arch
sudo pacman -S postgresql
# Initialise Server Data
sudo -u postgres initdb -D '/var/lib/postgres/data'
Debian/Mint/Ubuntu
sudo apt install postgresql
Fedora
sudo dnf install postgresql-server
# Initialise Server Data
sudo /usr/bin/postgresql-setup --initdb
Edit database access config
sudo nano /var/lib/pgsql/data/pg_hba.conf
Find these lines:
# IPv4 local connections:
host all all 127.0.0.1/32 ident
# IPv6 local connections:
host all all ::1/128 ident
Make sure the last item on each line (in this case ident) is changed to md5. Then restarted the database sudo systemctl restart postgresql.service
Podman (Universal)
Podman is a container system very similar to docker that lets you run programs in an isolated and self-contained environment.
First install Podman on your computer.
The container image will automatically be downloaded when you run postgres database with:
podman run -d --name postgres -p 5432:5432 -e POSTGRES_PASSWORD=mysecretpassword docker.io/library/postgres:latest
Checkout the Podman document to learn how to manage containers.
Note: You can skip the Setup stage below, it is already setup inside the container
Setup
First make sure postgres is running:
systemctl status postgresql.service
If not, start it with:
sudo systemctl start postgresql.service
If you want it to start on boot, you can enable it with:
sudo systemctl enable postgresql.service
To stop it from starting on boot, replace enable with disable.
Initially the postgres database user will not have a password to allow access. To access the database run the following:
sudo -u postgres psql
From here you can do initial setup.
For a simple setup where you just want to access from a client like pgAdmin, you can set a password for the postgres user with the following query:
ALTER USER postgres WITH PASSWORD 'new_password';
When done you can exit with the \q command.
pgAdmin
pgAdmin is a graphical interface to configure and query postgresql.
Install
Most linux distributions don't include pgAdmin from their default repositories, but we can either use pgAdmin's official repositories, or it is available as a flatpak:
Flatpak (universal)
If you wish to install through flatpak ensure flatpak is setup, see Flatpak for details if not.
If you have a graphical software manager/store it should be available there, to install via command-line:
flatpak install org.pgadmin.pgadmin4
Arch (AUR Package)
yay -S pgadmin4-server-bin pgadmin4-desktop-bin
Debian/Ubuntu
# Install the public key for the repository (if not done previously):
curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg
# Create the repository configuration file:
echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" | sudo tee /etc/apt/sources.list.d/pgadmin4.list
# Refresh package list
sudo apt update
# Install pgAdmin
sudo apt install pgadmin4-desktop
Fedora
Note: You will be prompted to import the required signing keys
# Add the pgadmin repository
sudo rpm -i https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-fedora-repo-2-1.noarch.rpm
# Install pgAdmin
sudo dnf install pgadmin4-desktop
Linux Mint
# Install the public key for the repository (if not done previously):
curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg
# Create the repository configuration file:
echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/noble pgadmin4 main" | sudo tee /etc/apt/sources.list.d/pgadmin4.list
# Refresh package list
sudo apt update
# Install pgAdmin
sudo apt install pgadmin4-desktop
Setup
Initial Setup
First you will need to register your running server:
In the General tab enter a name for your postgres server, then in the connection tab enter localhost for Hostname/address and enter the password for the postgres user:
Now click save and pgAdmin should be ready to use as normal!
PSQL Tool Setup
You may need to point pgAdmin to your binary path, so that it knows where to find the psql tool.
In File -> Preferences, under "Binary paths", in the "PostgeSQL Binary Path" put /usr/bin for the version of postgres install, and click the Validate button (Clipboard with a checkmark) to ensure it works.
Click the Save button in the top left, and the PSQL tool should now function.