Backup database:

# for use with pg_restore
pg_dump --file=database.dump --format=custom -U username -D database
# plain-text SQL script file
pg_dump --file=database.dump -U username -D database

Restore database:

# when dump is created with format 'c' we have to use pg_restore
pg_restore --clean -d database database.dump -U username
# from a plain-text SQL
psql --file database.dump -d database -U username

Create an empty database:

createdb -T template0 -U username database
psql>create dababase;

Delete a database:

dropdb -U username database
psql> drop database;

List databases:

psql --list

Add a user:

CREATE USER user WITH PASSWORD 'password';

Change password of a user:

ALTER USER username WITH PASSWORD 'password'

View statistics like what statements are being made in 'realtime':

psql> select * from pg_stat_activity;

Reindex database indexes:
reindexdb --echo -h localhost -U username -d database

Check size of database:

psql> select pg_size_pretty(pg_database_size('database'));

Alter OWNER

ALTER DATABASE name OWNER TO new_owner

Don't ask for password from these CIDR-ADDRESS

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
host    all         all         192.168.1.27/32       trust
# IPv6 local connections:
host    all         all         ::1/128               trust

backup database:

mysqldump -u USERNAME DATABASE -p -h HOSTNAME > /tmp/mysql.dump

Delete a database:

mysql> drop database DATABASE_NAME;

Create an empty database:

mysql -u USERNAME DATABASE -p -h HOSTNAME
create database `DATABASE` charset utf8;

Create user and privileges

CREATE USER username IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO username;
FLUSH PRIVILEGES;

Change password

mysql -u root mysql
mysql>update user set password=PASSWORD("NEWPASSWORD") where User='user';

restore database:

mysql -u USERNAME DATABASE -p -h HOSTNAME < /tmp/mysql.dump

Databases sizes

SELECT table_schema "Data Base Name", sum( data_length + index_length ) \
 / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema;

show tables

.tables