Installation#
To install PostgreSQL server:
$ dnf install postgresql-server
To install PostgreSQL client:
$ dnf install postgresql
To install PostgreSQL JDBC driver:
$ dnf install postgresql-jdbc
The driver is located at
/usr/share/java/postgresql-jdbc/postgresql.jar
.
Configuration#
The configuration files are located at:
/var/lib/pgsql/data/postgresql.conf
/var/lib/pgsql/data/pg_hba.conf
To enable remote access, edit /var/lib/pgsql/data/postgresql.conf as follows:
listen_addresses = '*'
and edit /var/lib/pgsql/data/pg_hba.conf as follows:
#host all all 127.0.0.1/32 ident
host all all 127.0.0.1/32 md5
#host all all ::1/128 ident
host all all ::1/128 md5
host all all 0.0.0.0/0 md5
then restart the server:
$ systemctl restart postgresql
Initializing Database#
To initialize PostgreSQL:
$ postgresql-setup --initdb --unit postgresql
Systemd Service#
To start the PostgreSQL service:
$ systemctl start postgresql
To stop the PostgreSQL service:
$ systemctl stop postgresql
Connecting to Database#
To connect to the local server as the root user:
$ sudo -u postgres psql
To connect to a specific database on a local server as the root user:
$ sudo -u postgres psql -d <database>
To connect to a specific database on a local server as a regular user:
$ sudo -u <username> psql -d <database>
To connect to a specific database on a remote server:
$ psql -h <hostname> -p <port> -d <database> -U <username>
or
$ psql postgres://<username>:<password>@<hostname>:<port>/<database>
To execute a script:
$ psql ... -f <path>
Managing Users#
To list users:
psql> \du
To create a user:
psql> create user <username> with password '<password>';
To drop a user:
psql> drop user <username>;
Managing Databases#
To list databases:
psql> \l
To create a database:
psql> create database <datatabase> owner <username>;
To show the current database:
psql> select current_database();
To switch to another database:
psql> \c <database>
To drop a database;
psql> drop database <database>;
Listing Tables#
psql> \dt
Describing a Table#
psql> \d <name>
Container#
To run PostgreSQL container:
$ podman run \
--name postgres \
--rm \
--network example \
-e POSTGRES_PASSWORD=Secret.123 \
-it \
postgres