Postgres Useful Tips

These are collection of useful tips dealing with Postgres day to day basis.

1. Backup & Restore

Fastest way

pg_dump -d 'postgres://username:password@hostname:port/database' -Fd -j 4 -f db_backup/data_220826

pg_restore --no-acl --no-owner -Fd -j4 -d 'postgres://username:password@hostname:port/database' db_backup/data_220826

pg_dump -Fc yourdb > dumpfc.gz
real 6m28.497s
user 5m2.275s
sys 1m16.637s

pg_restore -d yourdb -j 8 dumpfc.gz
real 16m49.539s
user 1m1.344s
sys 0m39.522s

Another way

pg_dump DATABASENAME | gzip -9 > DATABASENAME.sql.gz

gunzip < DATABASENAME.sql.gz | psql DATABASENAME

2. Get other server to access

TLDR: Add the IP in the pg_hba.conf file

Usualy pg_hba file lives here in Ubuntu

cd /etc/postgresql/15/main

Then open pg_hba.conf file and add IP address you want. Here the IP is 10.0.0.105

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             10.0.0.105/32      scram-sha-256

3. Environment variables

If you set these environment varialbes, you don’t have to give postgres host, user, password, ports to psql, pgdump, pgrestore commands

export PGUSER=postgres
export PGPASSWORD=postgres
export PGHOST=localhost
export PGPORT=5432

export PGDATABASE=yourdb

Put it in either .bashrc file or your .env file on your project root. See direnv with .env to setup .env for more tip.

Use this information to come up with others

DB_URL=postgresql://$PGUSER:$PGPASSWORD@PGHOST:$PGPORT/$PGDATABASE

See this for more info:

4. Check the size of the database

Run this SQL query in psql

SELECT pg_database.datname,
pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database;

postgres=# SELECT pg_database.datname,
pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database;
  datname  |  size
-----------+---------
 postgres  | 7437 kB
 yourdb    | 32 GB
 template1 | 7509 kB
 template0 | 7281 kB

If you want to check the size of one database:

select pg_size_pretty(pg_database_size('yourdb'))

postgres=# select pg_size_pretty(pg_database_size('yourdb'));
 pg_size_pretty
----------------
 32 GB
(1 row)

5. Check the size of tables

pg_relation_size('my_table');

SELECT pg_size_pretty(pg_relation_size('my_table'));

SELECT pg_size_pretty (pg_total_relation_size ('bike_details'));

6. Set pager on and off

\pset pager off
\pset pager on

7. Change user password

8. set up first user

➜  ~ psql postgres
psql (14.9 (Homebrew))
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 kjune     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# create role postgres with login password 'postgres';
CREATE ROLE
postgres=# alter role postgres superuser;
ALTER ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 kjune     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 postgres  | Superuser                                                  | {}

postgres=# \q

9. install postgis extension

psql (14.9 (Homebrew))
Type "help" for help.

postgres=# create extension postgis;
CREATE EXTENSION
postgres=# select postgis_version();
            postgis_version
---------------------------------------
 3.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

10. Completely remove postgres on homebrew mac

$ brew uninstall postgres
$ rm -rf /usr/local/var/postgres
$ rm /usr/local/var/log/postgres.log
$ rm -f ~/.psqlrc ~/.psql_history

11. Install

On Mac

1. install brew first
2. install postgres with brew

brew install postgresql
brew install postgresql@14

3. run brew with brew services

brew services list
brew services start postgresql
brew services stop postgresql
brew services restart postgresql

On Linux

1. get the key

2. install
3. install postgis

sudo apt install postgis postgresql-14-postgis-3

12. File Locations

On Mac

# brew install directory
/usr/local/Cellar/postgresql/14.5_4/bin
/usr/local/var/postgres

# log directory
/usr/local/var/log/[email protected]

/usr/local/var/postgres is where the database and logs are stored

/usr/local/opt/postgresql contains a .plist file which contains some metadata about how to launch Postgres.

1. config file

2. data directory
3. log file

Reason: tried: '/usr/local/opt/postgresql/lib/libpq.5.dylib' (no such file), '/usr/local/lib/libpq.5.dylib' (no such file), '/usr/lib/libpq.5.dylib' (no such file)

cd /usr/local/lib
ln -s /usr/local/opt/postgresql@14/lib/postgresql@14/libpq.5.dylib libpq.5.dylib

On Linux

1. config file
2. data directory
3. log file

13. fastest way with binary du p file

# dump
pg_dump -Fc -Z 9 --file=file.dump myDb
# restore
pg_restore -Fc -j 8  file.dump

FC: -Z 9: -j 8:

14. pgdump with gzip compression

$ pg_dump -Z0 -j 10 -Fd database_name -f dumpdir
$ tar -cf - dumpdir | pigz > dumpdir.tar.gz
$ rm dumpdir

$ pigz -dc dumpdir.tar.gz | tar -C dumpdir --strip-components 1 -xf -
$ pg_restore -j 10 -Fd -O -d database_name dumpdir

dump data only

pg_dump --column-inserts --data-only --table=<table> <database>

dump schema only

pg_dump -S <database>

17. pg_activity

https://github.com/dalibo/pg_activity

pg_activity -d zoomtrend --no-client

18. Create a copy of a database

CREATE DATABASE [Database to create]
WITH TEMPLATE [Database to copy]
OWNER [Your username];

19. Handling common error:

2022-09-23 22:57:09.863 PDT [70775] FATAL:  lock file "postmaster.pid" already exists
2022-09-23 22:57:09.863 PDT [70775] HINT:  Is another postmaster (PID 1268) running in data directory "/usr/local/var/postgres"?