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.

postgres=#


ii) bash-3.2$ psql postgres
psql (9.6.1)
Type "help" for help.

postgres=#
postgres=# select current_user;
 current_user
--------------
 anand
(1 row)


iii) URI format

bash-3.2$ psql postgresql://localhost:5432/postgres
psql (9.6.1)
Type "help" for help.

postgres=#
postgres=# select current_database();
 current_database
------------------
 postgres
(1 row)

postgres=# select inet_server_addr(), inet_server_port();
 inet_server_addr | inet_server_port
------------------+------------------
 ::1              |             5432
(1 row)

postgres=# select version();
                                                    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=#
postgres=# \conninfo
You are connected to database "postgres" as user "anand" on host "localhost" at port "5432".
postgres=#

PgAdmin4 and phpPgAdmin are Graphical Administrations tools. For more details

https://www.pgadmin.org/

Please note, when connecting with PgAdmin you should uncheck the “Strore Password” box.

A password file contains 5 fields

host:port:dbname:user:password

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
bash-3.2$

Next, I will writing on exploring the PostgreSQL db.

Advertisements