PostgreSQL quick memo

What is PostgresSQL  

postgreSQL is
open source object-relational database management system.

it's feature

• User-defined types
• Table inheritance
• Sophisticated locking mechanism
• Foreign key referential integrity
• Views, rules, subquery
• Nested transactions (savepoints)
• Multi-version concurrency control (MVCC)
• Asynchronous replication
• Native Microsoft Windows Server version
• Tablespaces
• Point-in-time recovery

where pg_hba.conf is

 /etc/postgresql/10/main/pg_hba.conf

Start postgres

$ sudo service postgresql start
or

Connect to the database

$sudo -u postgres bash

$psql postgres

once it started,
try to connect

$ sudo -u postgres psql template1

psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1))
Type "help" for help. template1=#
template1=#
template1=# help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
template1=#

The above command connects to PostgreSQL database template1 as user postgres. Once you connect to the PostgreSQL server, you will be at a SQL prompt. You can run the following SQL command at the psql prompt to configure the password for the user postgres.  ref

pg_ctl

pg_ctl is a utility for initializing a PostgreSQL database cluster, starting, stopping, or restarting the PostgreSQL database server (postgres), or displaying the status of a running server. Although the server can be started manually, pg_ctl encapsulates tasks such as redirecting log output and properly detaching from the terminal and process group. It also provides convenient options for controlled shutdown.

to enable to use pg_ctl, add path to the command. ( edit ~/.bashrc )

PATH=$PATH:/usr/lib/postgresql/{version}/bin
export PATH

17 Practical psql Commands That You Don’t Want To Miss

command reference page