PostgreSQL commands

postgresql-commands.md

PostgreSQL commands

Install psql

i)

dnf install postgresql-server

ii)

sudo apt-get install -y postgresql-client
psql --version  
psql (PostgreSQL) 9.5.12

Connection

$ psql -h <hostname or ip address> -p <port number of remote machine> -d <database name which you want to connect> -U <username of the database server>

Exiting psql

\q

Which version of PostgreSQL am I running?

# which psql

Config files

Where config file is located

postgres=# SHOW config_file;

host-based authentication file - pg_hba.conf.

To get file path

postgres=# SHOW hba_file;

Set some setting

# alter system set ssl=on;

Restart the PostgreSQL Service

service postgresql restart

Request settings

# select name,setting,unit,source,context from pg_settings where name = 'logging_collector';

Logs

# show log_directory ;
# show data_directory ;
# show log_filename ;

Zero instead of null

select coalesce(max(column), 0) from mytable; 
coalesce(SUM(coalesce("tempTable2".ideaVoteCount, 0)), 0)

List of relations

Use the \dt or \dt+ command in psql to show tables in a specific database. For ex.,

postgres=# \dt+

Describe table

\d+ tablename

Checking for connections

# select pg_ssl.pid, pg_ssl.ssl, pg_ssl.version,
    pg_sa.backend_type, pg_sa.usename, pg_sa.client_addr
    from pg_stat_ssl pg_ssl
    join pg_stat_activity pg_sa
    on pg_ssl.pid = pg_sa.pid;

Check that connection is encrypted

# \x
# SELECT * FROM pg_stat_ssl;

Check current connection information

# \conninfo

SSL authorization and authentication params

PGSSLMODE=verify-full
PGSSLROOTCERT=/usr/local/etc/ssl/ca.crt
PGSSLCERT=/usr/local/etc/ssl/postgres.crt
PGSSLKEY=/usr/local/etc/ssl/postgres.key