changed:
-
<p>Backup database:</p>
<pre>
# 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
</pre>

<p>Restore database:</p>
<pre>
# 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
</pre>

<p>Create an empty database:</p>
<pre>
createdb -T template0 -U username database
psql>create dababase;
</pre>

<p>Delete a database:</p>
<pre>
dropdb -U username database
psql> drop database;
</pre>

<p>List databases:</p>
<pre>
psql --list
</pre>

<p>Add a user:</p>
<pre>
CREATE USER user WITH PASSWORD 'password';
</pre>

<p>Change password of a user:</p>
<pre>
ALTER USER username WITH PASSWORD 'password'
</pre>

<p>View statistics like what statements are being made in 'realtime':</p>
<pre>
psql> select * from pg_stat_activity;

<p>Reindex database indexes:</p>
reindexdb --echo -h localhost -U username -d database
</pre>

<p>Check size of database:</p>
<pre>
psql> select pg_size_pretty(pg_database_size('database'));
</pre>

<p>Don't ask for password from these CIDR-ADDRESS</p>
<pre>
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
</pre>


<b>MySQL</b>

<p>backup database:</p>
<pre>
mysqldump -u USERNAME DATABASE -p -h HOSTNAME > /tmp/mysql.dump
</pre>



<p>Create an empty database:</p>
<pre>
mysql -u USERNAME DATABASE -p -h HOSTNAME
create database `DATABASE` charset utf8;
</pre>


<p>Create user and privileges</p>
<pre>
CREATE USER username IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO username;
FLUSH PRIVILEGES;
</pre>


<p>restore database:</p>
<pre>
mysql -u USERNAME DATABASE -p -h HOSTNAME < /tmp/mysql.dump
</pre>


<p>Databases sizes</p>
<pre>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;
</pre>


<b>sqlite</b>

<p>show tables</p>
<pre>
.tables
</pre>

# 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

# 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

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

dropdb -U username database
psql> drop database;

psql --list

CREATE USER user WITH PASSWORD 'password';

ALTER USER username WITH PASSWORD 'password'

psql> select * from pg_stat_activity;

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

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

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

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

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

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

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

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;

.tables