Postgresql interesting commands (for CLI only ;-) )

gistfile1.txt

1 - Logging as postgress

$ sudo su - postgres

2 - Creating a new user

$ createuser user

3 - Changing/Setting user password

$ alter user postgres with password 'crazylongpassword';

4 - Changing configurations on config file

$ sudo vim /etc/postgresql/9.1/main/pg_hba.conf

# Database administrative login by Unix domain socket
local   all             postgres                                peer
local   all             drupal                                  trust

5 - Changing postgres to listen address

$ sudo vim /etc/postgresql/9.1/main/postgresql.conf

*** in the following line:

listen_addresses = 'localhost'    # what IP address(es) to listen on;

6 - Restarting postgres server/cluster

$ sudo service postgresql restart

7 - Creating a database

$ createdb -p 5432 -O drupal -U drupal -E UTF8 testingsiteone -T template0

8 - Dropping a database

$ dropdb -p 5432 -U drupal testingsiteone

9 - Dumping a database

$ pg_dump -p 5432 -h localhost -Fc -U drupal --no-owner testingsiteone > /tmp/testingsiteone_$(date +"%Y-%m-%d_%s").pgdump

10 - Restoring a database

pg_restore -p 5432 -h localhost -Fc -d testingsiteone -U drupal --no-owner < /tmp/path-to-the-file.pgdump

11 - Logging for command line SQL purposes

$ psql -d testingsiteone -U postgres

12 - Useful commands:

\l :List databases
\c database-name :List databases
\d :List tables in database
\d table-name  escribe table
\q : quit
select * from table-name :List table contents