SELECT user, pid, client_addr, query, query_start, NOW() - query_start AS elapsed
FROM pg_stat_activity
WHERE query != ''
-- AND EXTRACT(EPOCH FROM (NOW() - query_start)) > 1
ORDER BY elapsed DESC;
psql:
SELECT pg_size_pretty(pg_database_size(pg_database.datname)) AS size_in_mb,
pg_database.datname as database_name
FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;
With index use: pg_total_relation_size, insead of pg_database_size
size_in_mb | database_name
------------+---------------
7571 kB | template1
7499 kB | postgres
7345 kB | template0
(3 rows)
\c postgres
SELECT *
FROM pg_stat_activity
WHERE datname = 'mydb';
Kill connections to database if you want:
SELECT
pg_terminate_backend (pid)
FROM
pg_stat_activity
WHERE
datname = 'mydb';
su - postgres
psql
ALTER DATABASE oldb RENAME TO newdb;
su - postgres
gunzip < /tmp/db.sql.gz | psql mydb
apt install postgresql-common
/usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
apt update && apt upgrade
apt install postgresql-17
pg_ctlcluster 17 main start
pg_ctlcluster 17 main status
pg_ctl: server is running (PID: 1028)
/usr/lib/postgresql/17/bin/postgres "-D" "/var/lib/postgresql/17/main" "-c" "config_file=/etc/postgresql/17/main/postgresql.conf"
vim /etc/postgresql/17/main/pg_hba.conf
host all all 0.0.0.0/0 md5
host all all ::/0 md5
vim /etc/postgresql/17/main/postgresql.conf
listen_addresses = '*'
pg_ctlcluster 17 main restart
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
apt update
apt install postgresql-16
pg_dropcluster --stop 16 main
pg_upgradecluster --method=upgrade --link 15 main
pg_lsclusters
15 main 5433 down postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
16 main 5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | apt-key add -
apt update
apt install postgresql-9.4
postgresql check databases for errors
pg_dumpall -f /dev/null 2> /tmp/pg.err
su - postgres
/usr/bin/pg_ctl reload
SELECT pg_reload_conf();
yum install postgresql-server postgresql-contrib
postgresql-setup initdb
systemctl start postgresql
systemctl enable postgresql
psql
select * from pg_stat_activity;
Passwords are not encrypted, so:
su – postgres
psql
\l
\c DB_NAME
SELECT login, password FROM res_users WHERE login = ‘admin’;
or
SELECT login, password FROM res_users;