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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s