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)
Related: How to Dump SQL Result in MySQL to a Text File Using cli – Reset Root Password on MySQL Database – Some MySQL cli Syntax
