PostgresSQL Commands for CentOS/Linux & pg_hba.conf configuration

Most of the time we can not install or use pgadmin. I tend to forget simple commands for administering the database. Hopefully this blog will help as reference when working with PostgreSQL

Login as "postgres" to start using database:

# su - postgres

Create a new database:

$ createdb mydb

Drop database:

$ dropdb mydb

Access database:

$ psql mydb

Get help:

mydb=# \h

Quit:

mydb=# \q

Read command from file:

mydb=# \i input.sql

To dump a database:

$ pg_dump mydb > db.out

To reload the database:

$ psql -d database -f db.out

Dump all database:

# su - postgres
# pg_dumpall > /var/lib/pgsql/backups/dumpall.sql

Restore database:

# su - postgres
# psql -f /var/lib/pgsql/backups/dumpall.sql mydb

Show databases:

#psql -l
or
mydb=# \l;

Show users:

mydb=# SELECT * FROM "pg_user";

Show tables:

mydb=# SELECT * FROM "pg_tables";

Set password:

mydb=# UPDATE pg_shadow SET passwd = 'new_password' where usename = 'username';

Clean all databases (Should be done via a daily cron):

$ vacuumdb --quiet --all

pg_hba.conf

The pg_hba.conf file states who is allowed to connect to the database server and which authentication method must be used to establish the connection.This configuration is very useful when we try to connect database through pgadmin client.

Default pg_hba.conf

# TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 trust