Postgres

Tools

Version

select version();

Database Size

SELECT pg_size_pretty(pg_database_size(current_database())) as size;

See PostgreSQL Built-in Functions documentation for more detailed examples

Check

service postgresql status

Show current activity...

postgres=#  \x auto

Expanded display is used automatically.

postgres=#  SELECT * from pg_stat_activity ;

Start

As 'root'...

service postgresql start

service postgresql restart

As 'postgres'...

requires $PGDATA to be set

echo $PGDATA

/var/lib/pgsql/data

pg_ctl start

Stop

As 'root'...

service postgresql stop

As 'postgres'...

requires $PGDATA to be set

echo $PGDATA

/var/lib/pgsql/data

pg_ctl stop -m fast

or 

pg_ctl stop -m immediate                       

PostgreSQL's fast option is equivalent to Oracle's immediatePostgreSQL's immediate option is equivalent to Oracle's abort !!!

Local Login

su - postgres

psql

Remote Login

psql -h hostname -U username -d dbname

Initial Setup

--Locally as postgres


cd /var/lib/pgsql/data

cp postgresql.conf postgresql.conf.20190103

vi postgresql.conf

listen_addresses = '*' <---------------- by default is listen_addresses = 'localhost'


--Locally as root (restart PostgreSQL instance)


service postgresql stop

service postgresql start


--Locally as postgres


psql

postgres=# SHOW listen_addresses;

 listen_addresses

------------------

 *


postgres=# SHOW hba_file;

 hba_file

---------------------------------

 /var/lib/pgsql/data/pg_hba.conf


--Locally as postgres


cd /var/lib/pgsql/data

cp pg_hba.conf pg_hba.conf.20190103

vi pg_hba.conf  <-------------- add following lines to pg_hba.conf


# 2019-01-03 Allow remote connections if the user's password is correctly supplied.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

host    all             all              0.0.0.0/0               md5

host    all             all              ::/0                    md5


--Locally as root (restart PostgreSQL instance)


service postgresql stop

service postgresql start

psql Shortcuts

List databases...

\l

Connect to database...

\c databasename

List tables, views and sequences...

\d