PostgreSQL Administration
This post covers some standard PostgreSQL Administration tasks. PostgreSQL Version 17 running on a RHEL 8.10 VM is used.
Table of Contents
Useful commands
Get the version of the PostgreSQL server
You can get the version of the installed PostgreSQL server by running any of these commands:
/usr/pgsql-17/bin/postgres -V
/usr/pgsql-17/bin/pg_config --version
rpm -qa|grep postgres
su - postgres -c "psql -c 'select version();'"
Output:
# Sample Output
[root@lin1 ~]# /usr/pgsql-17/bin/postgres -V
postgres (PostgreSQL) 17.2
[root@lin1 ~]# /usr/pgsql-17/bin/pg_config --version
PostgreSQL 17.2
[root@lin1 ~]# rpm -qa|grep postgres
postgresql17-17.2-1PGDG.rhel8.x86_64
postgresql17-libs-17.2-1PGDG.rhel8.x86_64
postgresql17-server-17.2-1PGDG.rhel8.x86_64
[root@lin1 ~]# su - postgres -c "psql -c 'select version();'"
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 17.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-22), 64-bit
(1 row)
[root@lin1 ~]#command snippets
# view the latest logfile
tail -n 100 -F `ls -tr /var/lib/pgsql/17/data/log/*log|tail -1`
# run sql statement as root
sq
# perform a checkpoint
checkpoint;
# switch to a new WAL file
checkpoint; select pg_switch_wal();
# get archiver status
select * from pg_stat_archiver;
# list info about all WAL summaries
select * from pg_available_wal_summaries() order by end_lsn;
# get WAL summarizer state
select * from pg_get_wal_summarizer_state();
# create a restore point (rp1)
select pg_create_restore_point('before');
select pg_create_restore_point('after');
# list active sessions
select * from pg_stat_activity where state='active';
# show percentage of dead tuples of all tables
select schemaname, relname, n_live_tup, n_dead_tup, trunc(n_dead_tup * 100 / ((n_live_tup + n_dead_tup) + 0.00001)) pct_dead from pg_stat_all_tables order by pct_dead desc;
Sample output of some statements:
zabbix=# select schemaname, relname, n_live_tup, n_dead_tup, trunc(n_dead_tup * 100 / ((n_live_tup + n_dead_tup) + 0.00001)) pct_dead from pg_stat_all_tables order by pct_dead desc;
schemaname | relname | n_live_tup | n_dead_tup | pct_dead
--------------------+----------------------------+------------+------------+----------
public | ha_node | 1 | 19 | 94
public | graph_discovery | 8 | 28 | 77
public | trigger_discovery | 15 | 45 | 74
public | config | 1 | 2 | 66
public | host_inventory | 2 | 4 | 66
You can create and drop databases with these SQL commands:
# show all installed databases
su - postgres -c "psql -c 'select datname from pg_database;'"
# create a new database
su - postgres -c "psql -c 'create database db1;'"
# show all installed databases
su - postgres -c "psql -c 'select datname from pg_database;'"
# drop a existing database
su - postgres -c "psql -c 'drop database db1;'"
# show all installed databases
su - postgres -c "psql -c 'select datname from pg_database;'"
Output:
[root@lin1 ~]# su - postgres -c "psql -c 'select datname from pg_database;'"
datname
-----------
postgres
template1
template0
(3 rows)
[root@lin1 ~]# # create a new database
[root@lin1 ~]# su - postgres -c "psql -c 'create database db1;'"
CREATE DATABASE
[root@lin1 ~]# # show all installed databases
[root@lin1 ~]# su - postgres -c "psql -c 'select datname from pg_database;'"
datname
-----------
postgres
db1
template1
template0
(4 rows)
[root@lin1 ~]# # drop a existing database
[root@lin1 ~]# su - postgres -c "psql -c 'drop database db1;'"
DROP DATABASE
[root@lin1 ~]# # show all installed databases
[root@lin1 ~]# su - postgres -c "psql -c 'select datname from pg_database;'"
datname
-----------
postgres
template1
template0
(3 rows)
[root@lin1 ~]#or by using the commands createdb and dropdb:
# show all installed databases
su - postgres -c "psql -c 'select datname from pg_database;'"
# create a new database
su - postgres -c 'createdb db1'
# show all installed databases
su - postgres -c "psql -c 'select datname from pg_database;'"
# drop a existing database
su - postgres -c 'dropdb db1'
# show all installed databases
su - postgres -c "psql -c 'select datname from pg_database;'"
Output:
[root@lin1 ~]# # show all installed databases
[root@lin1 ~]# su - postgres -c "psql -c 'select datname from pg_database;'"
datname
-----------
postgres
template1
template0
(3 rows)
[root@lin1 ~]# # create a new database
[root@lin1 ~]# su - postgres -c 'createdb db1'
[root@lin1 ~]# # show all installed databases
[root@lin1 ~]# su - postgres -c "psql -c 'select datname from pg_database;'"
datname
-----------
postgres
template1
template0
db1
(4 rows)
[root@lin1 ~]# # drop a existing database
[root@lin1 ~]# su - postgres -c 'dropdb db1'
[root@lin1 ~]# # show all installed databases
[root@lin1 ~]# su - postgres -c "psql -c 'select datname from pg_database;'"
datname
-----------
postgres
template1
template0
(3 rows)
[root@lin1 ~]#Also this can be used to list all databases of a server (cluster):
su - postgres -c "psql -c '\l'"
Create and drop a schema
# list all schemas of the current database
su - postgres -c "psql postgres -c '\dn'"
# create a schema in the current database
su - postgres -c "psql postgres -c 'create schema s1;'"
# list all schemas of the current database
su - postgres -c "psql postgres -c '\dn'"
# drop a schema in the current database
su - postgres -c "psql postgres -c 'drop schema s1;'"
# list all schemas of the current database
su - postgres -c "psql postgres -c '\dn'"
Output:
[root@lin1 ~]# # list all schemas of the current database
[root@lin1 ~]# su - postgres -c "psql postgres -c '\dn'"
List of schemas
Name | Owner
--------+-------------------
public | pg_database_owner
(1 row)
[root@lin1 ~]# # create a schema in the current database
[root@lin1 ~]# su - postgres -c "psql postgres -c 'create schema s1;'"
CREATE SCHEMA
[root@lin1 ~]# # list all schemas of the current database
[root@lin1 ~]# su - postgres -c "psql postgres -c '\dn'"
List of schemas
Name | Owner
--------+-------------------
public | pg_database_owner
s1 | postgres
(2 rows)
[root@lin1 ~]# # drop a schema in the current database
[root@lin1 ~]# su - postgres -c "psql postgres -c 'drop schema s1;'"
DROP SCHEMA
[root@lin1 ~]# # list all schemas of the current database
[root@lin1 ~]# su - postgres -c "psql postgres -c '\dn'"
List of schemas
Name | Owner
--------+-------------------
public | pg_database_owner
(1 row)Create and drop a table
# create a table t1 in the default schema 'public' with a column a (integer)
su - postgres -c "psql postgres -c 'create table t1 (a integer);'"
# describe the table (e.g. column and datatype info)
su - postgres -c "psql postgres -c '\d+ t1'"
# display info about a table (e.g. Size)
su - postgres -c "psql postgres -c '\dt+ t1'"
# drop the table
su - postgres -c "psql postgres -c 'drop table t1;'"
Output:
[root@lin1 ~]# # create a table t1 in the default schema 'public' with a column a (integer)
[root@lin1 ~]# su - postgres -c "psql postgres -c 'create table t1 (a integer);'"
CREATE TABLE
[root@lin1 ~]# # describe the table (e.g. column and datatype info)
[root@lin1 ~]# su - postgres -c "psql postgres -c '\d+ t1'"
Table "public.t1"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
a | integer | | | | plain | | |
Access method: heap
[root@lin1 ~]# # display info about a table (e.g. Size)
[root@lin1 ~]# su - postgres -c "psql postgres -c '\dt+ t1'"
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------+-------+----------+-------------+---------------+---------+-------------
public | t1 | table | postgres | permanent | heap | 0 bytes |
(1 row)
[root@lin1 ~]# # drop the table
[root@lin1 ~]# su - postgres -c "psql postgres -c 'drop table t1;'"
DROP TABLECreate and drop a tablespace
cat << EOF|su - postgres
echo === create a directory for the tablespace ===
mkdir /var/lib/pgsql/17/data2
echo === create the tablespace ts1 ===
psql postgres -c "create tablespace ts1 location '/var/lib/pgsql/17/data2';"
echo === list the contents of the directory ===
ls -al /var/lib/pgsql/17/data2
echo === list all tablespaces of the postgres database ===
psql postgres -c '\db+'
echo === drop the tablespace ts1 ===
psql postgres -c 'drop tablespace ts1;'
echo === list the contents of the directory ===
ls -al /var/lib/pgsql/17/data2
echo === remove the directory ===
rmdir /var/lib/pgsql/17/data2
EOF
Output:
=== create a directory for the tablespace ===
=== create the tablespace ts1 ===
CREATE TABLESPACE
=== list the contents of the directory ===
total 12
drwx------ 3 postgres postgres 4096 Jan 27 13:00 .
drwx------ 5 postgres postgres 4096 Jan 27 13:00 ..
drwx------ 2 postgres postgres 4096 Jan 27 13:00 PG_17_202406281
=== list all tablespaces of the postgres database ===
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+-------------------------+-------------------+---------+---------+-------------
pg_default | postgres | | | | 30 MB |
pg_global | postgres | | | | 565 kB |
ts1 | postgres | /var/lib/pgsql/17/data2 | | | 0 bytes |
(3 rows)
=== drop the tablespace ts1 ===
DROP TABLESPACE
=== list the contents of the directory ===
total 8
drwx------ 2 postgres postgres 4096 Jan 27 13:00 .
drwx------ 5 postgres postgres 4096 Jan 27 13:00 ..
=== remove the directory ===
[root@lin1 ~]#Creating sample data
Loading 1.2 GB data with pg_restore
We can download a dump that was created with pg_dump -Fc. It contains data from IMDb. Place it in a directory accessible to the PostgresSQL server. Then we import the data into our server:
su - postgres -c "psql -c 'create role imdb;'"
su - postgres -c 'pg_restore -c -C -j 4 -d postgres /sw/PostgreSQL/imdb_pg11'
This takes around 3 minutes on my system with 4 parallel jobs. To remove the data created do this:
su - postgres -c "psql -c 'drop database imdb (force);'"
su - postgres -c "psql -c 'drop role imdb;'"
Creating sample data with pgbench
pgbench is a tool to perform benchmark tests on a PostgreSQL server.
(
# list all existing databases
su - postgres -c "psql -c '\l'";
echo ===;
read -p 'Enter the name of the new db: ' dbname;
# create the new database
su - postgres -c "psql -c 'create database $dbname;'";
# create the sample data
# 4 tables are created. The table pgbench_accounts will contain 1m rows.
su - postgres -c 'pgbench -s 10 -i $dbname';)
Output:
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
postgres | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
template0 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(3 rows)
===
Enter the name of the new db: db1
CREATE DATABASE
dropping old tables...
creating tables...
generating data (client-side)...
vacuuming...
creating primary keys...
done in 2.09 s (drop tables 0.03 s, create tables 0.01 s, client-side generate 1.43 s, vacuum 0.15 s, primary keys 0.47 s).
[root@lin1 ~]#Just for completeness this is how we perform a benchmark test simulating 50 concurrent clients running 500 transactions each:
su - postgres -c 'pgbench -c 50 -t 500 pgb'
Output:
[root@lin1 17]# su - postgres -c 'pgbench -c 50 -t 500 pgb'
pgbench (17.2)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 50
number of threads: 1
maximum number of tries: 1
number of transactions per client: 500
number of transactions actually processed: 25000/25000
number of failed transactions: 0 (0.000%)
latency average = 13.557 ms
initial connection time = 138.484 ms
tps = 3688.045084 (without initial connection time)
[root@lin1 17]#To remove the data created do this:
(su - postgres -c "psql -c '\l'"
echo ===
read -p 'Enter the name of the db to drop: ' dbname
su - postgres -c "psql -c 'drop database $dbname (force);'")
Backup and Restore
Backup and Restore with SQL Dumps
Backup and restore one database
For this demo we create the sample database pdb. After that we can backup this database with the following command:
su - postgres -c 'pg_dump pgb > /var/tmp/pgb.dmp'
Now we drop and recreate the database before we can restore the backup:
su - postgres -c "psql -c 'drop database pgb (force);'"
su - postgres -c "psql -c 'create database pgb;'"
Next step is the complete restore of the contents of the pgb database:
su - postgres -c "psql --set ON_ERROR_STOP=on pgb < /var/tmp/pgb.dmp"
Output:
[root@lin1 ~]# su - postgres -c "psql --set ON_ERROR_STOP=on pgb < /var/tmp/pgb.dmp"
SET
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
COPY 1000000
COPY 10
COPY 0
COPY 100
ALTER TABLE
ALTER TABLE
ALTER TABLE
[root@lin1 ~]#Backup and restore all databases
All databases and global defined objects like roles, tablespaces,.. can be backed up with:
su - postgres -c 'pg_dumpall > /var/tmp/da.dmp'
Before we restore we will deinstall and create a new PostgreSQL server. Afterwards we restore all objects with:
su - postgres -c 'psql -f /var/tmp/da.dmp postgres'
Output:
[root@lin1 ~]# su - postgres -c 'psql -f /var/tmp/da.dmp postgres'
SET
SET
SET
psql:/var/tmp/da.dmp:14: ERROR: role "postgres" already exists
ALTER ROLE
You are now connected to database "template1" as user "postgres".
SET
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
CREATE DATABASE
ALTER DATABASE
You are now connected to database "pgb" as user "postgres".
SET
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
COPY 1000000
COPY 10
COPY 0
COPY 100
ALTER TABLE
ALTER TABLE
ALTER TABLE
You are now connected to database "postgres" as user "postgres".
SET
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
[root@lin1 ~]#Backup and Restore with continuous archiving
Continuous archiving saves the WAL files and enables the database to be restored to a time between the backup and the current time. Restore points can be created to mark specific points in time to which you can restore.
Enable WAL archiving
WAL archiving needs to be enabled for the PostgreSQL cluster before performing a backup/restore with continuous archiving:
# create a directory for the archive files
mkdir /var/tmp/arch && chown postgres:postgres /var/tmp/arch && chmod 700 /var/tmp/arch
# enable wal_level = replica
sed -i '/^#wal_level/s/^# *//' /var/lib/pgsql/17/data/postgresql.conf
# set archive_mode = on
sed -i '/^#archive_mode/s/^#archive_mode = off */archive_mode = on/' /var/lib/pgsql/17/data/postgresql.conf
# set the archive command (cp)
sed -i "/^#archive_command = ''/s/^#archive_command = '' */archive_command = 'test ! -f \/var\/tmp\/arch\/%f \&\& cp %p \/var\/tmp\/arch\/%f'/" /var/lib/pgsql/17/data/postgresql.conf
# enable WAL summarization (needed for incremental backups)
sed -i '/^#summarize_wal/s/^#summarize_wal = off/summarize_wal = on/' /var/lib/pgsql/17/data/postgresql.conf
# restart the db server
systemctl restart postgresql-17
Archive files will be created once a log switch occurs. To force a log switch we can run this:
# check current archive files
ls -al /var/tmp/arch
# force a log switch
su - postgres -c "psql -c 'checkpoint; select pg_switch_wal();'"
# verify the created archive file
ls -al /var/tmp/arch
We can use the following function to get some info about the archives:
su - postgres -c "psql -c 'select * from pg_stat_archiver;'"
Creating a Full Backup
Let’s create a base backup. All WAL archives from the beginning of the backup until the time you want to be able to restore the database need to be saved.
rm -rf /var/tmp/bb
# disable WAL summarization
su - postgres -c "psql -c 'alter system set summarize_wal='off';'" && su - postgres -c "pg_ctl reload"
# creating the full backup
su - postgres -c 'pg_basebackup -Ft -z -D /var/tmp/bb'
# enable WAL summarization
su - postgres -c "psql -c 'alter system set summarize_wal='on';'" && su - postgres -c "pg_ctl reload"
ls -alh /var/tmp/bb
Output:
[root@lin1 ~]# rm -rf /var/tmp/bb
[root@lin1 ~]# su - postgres -c 'pg_basebackup -Ft -z -D /var/tmp/bb'
[root@lin1 ~]# ls -alh /var/tmp/bb
total 13M
drwx------ 2 postgres postgres 4.0K Jan 28 18:56 .
drwxrwxrwt. 13 root root 4.0K Jan 28 18:56 ..
-rw------- 1 postgres postgres 179K Jan 28 18:56 backup_manifest
-rw------- 1 postgres postgres 13M Jan 28 18:56 base.tar.gz
-rw------- 1 postgres postgres 17K Jan 28 18:56 pg_wal.tar.gz
[root@lin1 ~]#Creating a Incremental Backups
Incremental backups can provide benefits if the database is large. We create a incremental backup with:
rm -rf /var/tmp/ib1
su - postgres -c "psql -c 'checkpoint;'"
su - postgres -c 'pg_basebackup --incremental=/var/tmp/bb/backup_manifest -Ft -z -D /var/tmp/ib1'
ls -alh /var/tmp/ib1
Output:
[root@lin1 ~]# rm -rf /var/tmp/ib1
[root@lin1 ~]# su - postgres -c 'pg_basebackup --incremental=/var/tmp/bb/backup_manifest -Ft -z -D /var/tmp/ib1'
[root@lin1 ~]# ls -alh /var/tmp/ib1
total 280K
drwx------ 2 postgres postgres 4.0K Jan 28 18:57 .
drwxrwxrwt. 14 root root 4.0K Jan 28 18:57 ..
-rw------- 1 postgres postgres 188K Jan 28 18:57 backup_manifest
-rw------- 1 postgres postgres 62K Jan 28 18:57 base.tar.gz
-rw------- 1 postgres postgres 17K Jan 28 18:57 pg_wal.tar.gz
[root@lin1 ~]#Restore the database
With these steps we restore the database from the backup and apply all available WAL archives. This gives us the most up to date database:
# stop the db server
systemctl stop postgresql-17
# backup the not yet archived WAL files
su - postgres -c 'mkdir -p /var/tmp/arch2 && rm -rf /var/tmp/arch2/* && cp -rp /var/lib/pgsql/17/data/pg_wal/* /var/tmp/arch2'
# remove all files and directories in the data directory
rm -rf /var/lib/pgsql/17/data/*
# Option1: restore the last base backup and apply all WAL archives
ls -alh /var/lib/pgsql/17/data/|wc -l
cat /var/tmp/bb/base.tar.gz|gunzip -|tar -xf - -C /var/lib/pgsql/17/data/
ls -alh /var/lib/pgsql/17/data/|wc -l
# Option2: restore the last base backup and the incremental backup and apply all necessary WAL archives
## extract backups to temporary directories
su - postgres -c 'mkdir -p /var/tmp/ebb && rm -rf /var/tmp/ebb/* && chown postgres:postgres /var/tmp/ebb'
su - postgres -c 'mkdir -p /var/tmp/eib1 && rm -rf /var/tmp/eib1/* && chown postgres:postgres /var/tmp/eib1'
su - postgres -c 'cat /var/tmp/bb/base.tar.gz|gunzip -|tar -xf - -C /var/tmp/ebb/ && cp /var/tmp/bb/backup_manifest /var/tmp/ebb/'
su - postgres -c 'cat /var/tmp/ib1/base.tar.gz|gunzip -|tar -xf - -C /var/tmp/eib1/ && cp /var/tmp/ib1/backup_manifest /var/tmp/eib1/'
## run pg_combinebackup and cleanup temporary directories
su - postgres -c 'pg_combinebackup -o /var/lib/pgsql/17/data/ /var/tmp/ebb /var/tmp/eib1 && rm -rf /var/tmp/ebb /var/tmp/eib1'
If you want to stop the restore at a specific point in time, you can specify a time:
(read -p "Enter restore time (e.g. 2025-01-30 12:45:00 Europe\\\/Berlin ): " rt
sed -i "/^#recovery_target_time/s/#recovery_target_time = ''/recovery_target_time = '$rt'/" /var/lib/pgsql/17/data/postgresql.conf
sed -i "/^#recovery_target_action/s/#recovery_target_action = 'pause'/recovery_target_action = 'promote'/" /var/lib/pgsql/17/data/postgresql.conf)
or a restore point as follows:
(read -p 'Enter restore point name: ' rpn
sed -i "/^#recovery_target_name/s/#recovery_target_name = ''/recovery_target_name = '$rpn'/" /var/lib/pgsql/17/data/postgresql.conf
sed -i "/^#recovery_target_action/s/#recovery_target_action = 'pause'/recovery_target_action = 'promote'/" /var/lib/pgsql/17/data/postgresql.conf)
Then start the recovery process:
# copy WAL files saved earlier
su - postgres -c 'rm -rf /var/lib/pgsql/17/data/pg_wal/* && cp -rp /var/tmp/arch2/* /var/lib/pgsql/17/data/pg_wal'
# disallow clients to connect during recovery
sed -i '/^host all all 0.0.0.0\/0/s/host/#host/' /var/lib/pgsql/17/data/pg_hba.conf
# enable recovery settings
su - postgres -c 'touch /var/lib/pgsql/17/data/recovery.signal'
sed -i "/^#restore_command/s/#restore_command = ''/restore_command = 'cp \/var\/tmp\/arch\/%f %p'/" /var/lib/pgsql/17/data/postgresql.conf
# start the server to start the recovery
systemctl start postgresql-17
# wait until recovery is finished (recovery.signal will be removed by the server)
while [ -f /var/lib/pgsql/17/data/recovery.signal ] ; do echo 'applying WAL archive files...' ; sleep 3 ; done
# reset params
sed -i "/^recovery_target_name = /s/recovery_target_name = '.*'/#recovery_target_name = ''/" /var/lib/pgsql/17/data/postgresql.conf
sed -i "/^recovery_target_time = /s/recovery_target_time = '.*'/#recovery_target_time = ''/" /var/lib/pgsql/17/data/postgresql.conf
# enable clients to connect and restart PostgreSQL server
sed -i '/^#host all all 0.0.0.0\/0/s/#host/host/' /var/lib/pgsql/17/data/pg_hba.conf
systemctl restart postgresql-17
# enable WAL summarization
su - postgres -c "psql -c 'alter system set summarize_wal='on';'" && su - postgres -c "pg_ctl reload"
During the recovery we can monitor the process by taking a look at the current server logfile.
Routine Maintenance Tasks
Routine maintenance tasks like vacuum and vacuum analyze deserve more attention than what can be covered in this post, so I will create separate posts in the category PostgreSQL.
In summary we can say, that we should run vacuum analyze periodically to remove dead rows and update statistics so that the query planner can make good plans.
# run vacuum (removes dead rows from tables and indexes but releases the space
# to the OS only in case the dead rows were at the end of the object)
vacuum table1; # SQL statement, for one table
vacuumdb -a # client application, for all databases
# run vacuum full (removes dead rows from tables and indexes and releases space back to the OS)
vacuum full pgbench_accounts; # SQL statement, for one table
vacuumdb -fa # client application, for all databases
# commands can be combined with analyze to also gather statistics
vacuum full analyze table1; # SQL statement, for one table
vacuumdb -faz # client application, for all databases
Leave a Reply