Postgresql

Test Database Connection

This is a way to test the database connection.

 ./pg_isready -h localhost -p 5432

Application Structure

Log location

./data/log/

OSX

The Postgres app live here on OSX

 /Library/PostgreSQL/11

Start

 sudo -u postgres ./bin/pg_ctl -D ./data start

Stop

 sudo -u postgres ./bin/pg_ctl -D ./data stop

FYI, haven't tried but look like a nicer app to control Postgres is https://postgresapp.com.

If you are not using the system registered database you can all simply use the following commands.

 pg_ctl -D ./data start
 pg_ctl -D ./data stop

Initialize a database directory

To initialize a database directory use the following command.

 initdb -D <data_directory>

Environment Path

To add PostgreSQL in to your environment path.

 export POSTGRESQL_HOME=/Library/PostgreSQL/11
 PATH=$PATH:$POSTGRESQL_HOME/bin

Debian

You can start Postgres in Debian using

 /etc/init.d/postgresql start

Create a database and user

 sudo su - postgres
 createdb testdb
 createuser testdb

To create a password for the user either use

 createuser testdb --pwprompt

or

 psql
 ALTER USER 'testdb' WITH PASSWORD 'testdb';

To have the new user be a superuser add the following.

 createuser --superuser testdb --pwprompt

Run file and commands from CLI

PSQL will ask for your password every time unless you set it as an environment variable.

 export PGPASSWORD=Password123

To run a command.

 psql -U user_name -d database_name -c "SELECT * FROM tblExample"

To run a file.

 psql -U user_name -d database_name -f ./file.sql

Issues

Error Connecting to database FATAL : no pg_hba.conf entry for host I had to add the host ip address into the pg_hba.conf file.

location of file: ./data/pg_hba.conf

Added the following line and then restarted Postgres.

 host    all             all             10.225.41.95/24        md5

Describe a table

You can query the 'information_schema.columns' table to find internal table information.

This is a sample command to find information about a table.

 SELECT table_name, column_name, data_type
 FROM information_schema.columns
 WHERE table_name='TABLE_A';

Top 10 rows

This is to get the top 10 rows.

 select *
 from table_a
 fetch first 10 rows only;

PSQL VIM

Tips for editing script and running them in th PSQL command.

1. Edit a script in vim. Once logged in use the following command.

 \e

2. Multiple windows. In 1 window connect using the psql tool and run scripts using the following command.

 \o ./sql.out \i ./test.sql

In 2 window using VIM, edit the test.sql file and open a new tab of sql.out. Then refresh the buffer using

 :e