A few Useful Postgresql Tips

Export data:

pg_dump -U testuser -h <db host> -p 5432 <db name > testdump.sql

Import data (plain sql file):

psql -U testuser <db name>  < testdump.sql> testImport.log

Connect to remote DB:

psql -U testuser -h  <db host> -p 5432 <db name>

Connect to remote DB with password

psql "dbname=<db name> user=postgres password=***  host=<db host> port=5432"

Grant privileges:

drop database testdb;
create database testdb;
create role testuser ;
alter role testuser  createdb;
alter role testuser login;
alter role testuser  createrole;

Grant an user to be a superuser:

ALTER USER testuser WITH SUPERUSER;

Check db connections in the Postgresql ie client_addr ,client_hostname

select * from pg_stat_activity  where datname = 'testdb';

Check db parameters ie max_connections

show max_connections;

Create a DB with UTF8 

CREATE DATABASE "teststg"
    WITH OWNER "postgres"
    ENCODING 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TEMPLATE template0;