Connecting to PostgreSQL

To connect to PostgreSQL, the following parameters are required:
1. Host or Host Address
2. Port
3. Database Name
4. User
5. Password
As mentioned, in my earlier post, like sqlplus in oracle, PostgreSQL has “psql”.
To connect any PostgreSQL db, you can use
1. psql
2. pgAdmin4
3. phpPgAdmin
Using psql
The default port number for PostgreSQL is 5432.

i) psql -h hostname -p port -d dbname -U username
bash-3.2$ psql -h localhost -p 5432 -d postgres -U anand
psql (9.6.1)
Type "help" for help.
ii) bash-3.2$ psql postgres
psql (9.6.1)
Type "help" for help.
postgres=# select current_user;
(1 row)
iii) URI format
bash-3.2$ psql postgresql://localhost:5432/postgres
psql (9.6.1)
Type "help" for help.
postgres=# select current_database();
(1 row)
postgres=# select inet_server_addr(), inet_server_port();
 inet_server_addr | inet_server_port
 ::1              |             5432
(1 row)
postgres=# select version();
 PostgreSQL 9.6.1 on x86_64-apple-darwin15.6.0, compiled by Apple LLVM version 8.0.0 (clang-800.0.42.1), 64-bit
(1 row)
postgres=# \conninfo
You are connected to database "postgres" as user "anand" on host "localhost" at port "5432".

PgAdmin4 and phpPgAdmin are Graphical Administrations tools. For more details
Please note, when connecting with PgAdmin you should uncheck the “Strore Password” box.
A password file contains 5 fields
The password file is located using an env variable PGPASSFILE. If its not set, then default filename and locationfor *nix system is ~/.pgpass. Make sure the file permission is 0600.
ls -lrt /Users/anand/.pgpass
In case the file is present, and you try to connect using psql, it won’t prompt for password.
To check connection status of PostgreSQL server, use pg_isready

bash-3.2$ pg_isready -h localhost -p 5433
localhost:5433 - no response
bash-3.2$ pg_isready -h localhost -p 5432
localhost:5432 - accepting connections

Next, I will writing on exploring the PostgreSQL db.

Leave a Reply