Skip to content

Instantly share code, notes, and snippets.

@shivampip
Last active October 13, 2021 07:30
Show Gist options
  • Save shivampip/e3d77e55e95e5c21f406cfd3cd5ad9a4 to your computer and use it in GitHub Desktop.
Save shivampip/e3d77e55e95e5c21f406cfd3cd5ad9a4 to your computer and use it in GitHub Desktop.
Postgresql Ubuntu Configuration

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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment