psql

ماذا يفعل

طريقة الكتابة

psql [-h HOST] [-p PORT] [-U USER] [-d DB] [-c SQL | -f FILE]
pg_dump -Fc -d DB -f FILE.dump

الأجزاء الرئيسية

psql meta-commands

\l

List databases.

\c DB

Connect to another database.

\dt / \d TABLE

List tables / describe a table.

\du

List roles.

\dn / \df / \dv

Schemas / functions / views.

\x

Expanded output (one field per line).

\timing

Show query time.

\i FILE

Run a SQL file.

\q

Quit.

\conninfo

Show the current connection.

الأكثر استخدامًا

لا يوجد شيء يطابق هذا الفلتر.

$ مستخدم عادي# بصلاحية root (أو مع sudo)

1. 1Install and start on RHEL

   #dnf install -y postgresql-server
   #postgresql-setup --initdb
   #systemctl enable --now postgresql
   #sudo -u postgres psql -c "SELECT version();"

2. 2Create an app role and database (least privilege)

   #sudo -u postgres psql
   postgres=# CREATE ROLE shop LOGIN PASSWORD 'Ch4nge-me-2026';
   postgres=# CREATE DATABASE shop OWNER shop;
   postgres=# REVOKE ALL ON DATABASE shop FROM PUBLIC;

3. 3Connect with TLS

   $psql "host=db1.lab.local dbname=shop user=shop sslmode=verify-full sslrootcert=/etc/pki/tls/certs/lab-ca.crt"

4. 4Look around

   $psql -h db1 -U shop -d shop -c '\dt'
   $psql -h db1 -U shop -d shop -c '\du'

5. 5Who is connected, what runs long?

   $psql -U postgres -c "SELECT pid, usename, state, now()-query_start AS age, left(query,60) FROM pg_stat_activity WHERE state <> 'idle' ORDER BY age DESC;"

6. 6Backup and restore one database

   $pg_dump -h db1 -U shop -Fc -d shop -f shop-$(date +%F).dump
   $pg_restore -h db2 -U postgres -d shop --clean --if-exists shop-2026-10-27.dump

7. 7Back up roles and all databases

   $pg_dumpall -h db1 -U postgres --roles-only > roles.sql

8. 8Allow only the app subnet with passwords (scram)

   #cat /var/lib/pgsql/data/pg_hba.conf
   hostssl  shop  shop  10.20.2.0/24  scram-sha-256
   #systemctl reload postgresql

9. 9Size of databases and tables

   $psql -U postgres -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY pg_database_size(datname) DESC;"

10. 10Run in a container for a lab

    $podman run -d --name pg -e POSTGRES_PASSWORD=labonly -p 127.0.0.1:5432:5432 docker.io/library/postgres:16

لا يوجد شيء يطابق هذا الفلتر.

الملفات

/var/lib/pgsql/data/postgresql.conf

Server settings (RHEL).

/var/lib/pgsql/data/pg_hba.conf

Who may connect how.

~/.pgpass

Saved passwords (chmod 600).

رموز الخروج