Ctrl+K

Installation

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

See Also#

Contents