Postgresql Ubuntu Configuration

postgresql.md

After INstallation

• Switch to postgres user

su - postgres

• Create new user name root (for example)

createuser --interactive --pwprompt

• Create DB

createdb -O root mydb

• exit postgres user using

exit

• Now open postgresql cli

psql -d mydb

Note: switch to postgres user if issue comes.

Make it connatable from other computer

• Add this line in /etc/postgresql/13/main/postgresql.conf

listen_addresses = '*'

• Replace first line with second line in /etc/postgresql/13/main/pg_hba.conf

host    all             all             127.0.0.1/32            md5

host    all             all             0.0.0.0/0            md5

CLI Commands

• List databases

\l

• List available tables in current db

\dt

• Switch database

\c tablename

• Create Table

CREATE TABLE leads (id INTEGER PRIMARY KEY, name VARCHAR);

• Quit

\q

Backup & Restore

• First switch to postgres user (su - postgres)
• Backup single db

pg_dump hellodb > hellodb.sql

• Backup all databases

pg_dumpall > all_dbs.sql

• Restore a db

psql hellodb < hellodb.sql

• Ref: https://www.tecmint.com/backup-and-restore-postgresql-database/