Some Quick cli Syntax for Postgres

How to Dump SQL result to a text file using cli

> psql -U postgres -d [database_name] -c ‘SELECT * FROM users’

How to exit postgres command-line utility psql
Type \q and then press ENTER to quit psql

Reset Root Password on MySQL Database
Notice that in PostgreSQL superuser is called postres (not root). If you forgot superuser password, you can reset it this way:

edit file pg_hba.conf

> sudo vim /etc/postgresql/9.3/main/pg_hba.conf

and find there a line similar to:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                md5

md5 here means that postgres asks for password. If there is this line then replace
md5 with peer, if there is no this line then add it

local   all             postgres                                peer

save the file, exit and reload postgresql service to pick up the updated configuration:

> sudo service postgresql reload

Then you should be able to get access to psql without providing a password this way:

> sudo -u postgres psql

In psql you can change user password using this command:

ALTER USER postgres PASSWORD 'new password';

PostgreSQL performance and monitoring

switch current database

\connect database_name or \c database_name

you can user autocomplete by pressing [tab]

list tables in current database

\dt

create a new database and import tables, data from a dump
in psql

CREATE DATABASE some_database

create a dump (in terminal)

> pg_dump dbname > outfile

You may notice that no password is used. That is because with Postgres you can setup a password file that is used to verify your access as such:

> vim ~/.pgpass

Then (format is hostname:port:database:username:password):

localhost:5432:mydbname:postgres:mypass
[ctrl-x] then confirm with y, then hit [enter]

save and exit

> chmod 600 ~/.pgpass

restore a dump (in terminal)

> psql dbname < infile

more details on dump-restore

create a user (it sql)

CREATE USER tester WITH PASSWORD 'test_password';
GRANT ALL PRIVILEGES ON DATABASE "test_database" to tester;

notice difference in quotation marks – in first case it must be single quotation mark ‘ and in the second case – double quotation mark “, otherwise PostgreSQL raises a syntax error

restart PostgreSQL

> sudo service postgresql restart

other regular service actions available too – [force-reload, reload, start, status, stop]

Also for Nginx (unrelated to this post really), Restart Nginx

sudo service nginx restart
other regular service actions available too – [force-reload, reload, start, status, stop]
reload only reloads configuration files, without stopping a service (which restart does)