How to install Postgresql Database in windows

 

Steps to install Postgresql Database in windows:

1.      Download PostgreSQL software from below link.

https://www.enterprisedb.com/download-postgresql-binaries

2.     

Click on win x86-64, software will download.

3.      Extract that software, extracted binaries looks as below.

4.      Copy this software to other drive from downloads.

5.      Create one directory as data to create database.

6.     

7.      Open a command prompt as administrator.

8.      Goto bin location of Postgresql software, copy that location go to that location from command prompt.

D:\postgresql\pgsql\bin

9.      Goto data directory and copy that path, execute like below

10. 

Initdb.exe -> is tool to create the database.

-D -> This option specifies the directory where the database cluster should be stored. 

-U -> Selects the user name of the superuser. This defaults to the name of the cluster owner.

 

Press enter, then it will create the database.

11.  Create the service for postgresql database in windows.

Pg_ctl -> is too to initialize, start, stop, or control a PostgreSQL server

Register -> using for registering the service in windows

12.  Verify the service in services, Go to search box and run services.msc

13. 

                                                                                                                                          

See the in services, postgresql service is created, to start the database click on start button .

Postgresql database is running now.

To connect the postgresql database open command prompt and run as like below.

Psql ->  PostgreSQL interactive terminal

-U -> Username with what user would like to connect the database.

To check the status of database

To stop the database from command prompt

To start the database from command prompt.

To connect the database.

To Quite the database

 

 

 

 

 

Read More

Connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

[postgres@srv data]$ psql -l

psql: could not connect to server: No such file or directory

              Is the server running locally and accepting

              Connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

That simple way is please update postgres home, bin, library details in  .bash_profile

[postgres@srv data]$cd 

[postgres@srv ~]$vi .bash_profile

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH

export PG_HOME=/home/postgres/pgsql

export PATH=$PG_HOME/bin:$PATH:.

export PG_DATA=/home/postgres/data

save the details using esc, :wq

[postgres@srv ~]$. .bash_profile

hopefully this could resolve the issue.

Read More

Postgresql 9.2 streaming replication step by step

Using below steps we can create postgresql high availability setup.

Postgres replication setup:

Primary server configuration:

step1: edit postgresql.conf

wal_level = hot_standby
max_wal_senders = 1
wal_keep_segments = 50
archive_mode = on
archive_command = 'cp %p /archive_log/archive/%f'

step2: edit pg_hba.conf

host replication postgres 192.168.X.X/32 trust ----DR server IP address

step3:restart postgres service

as a root user go to /etc/init.d/

#service postgresql-9.2 restart

step4:Next thing we need to do is take data snapshot of data from master and then move that to slave server.

psql -c "SELECT pg_start_backup('replbackup');"
(GO TO ROOT AND TAR DATA)
tar cfvP /opt/Postgresql/9.2/data.tar /opt/Postgresql/9.2/data
psql -c "SELECT pg_stop_backup();"

step5:Now move this data to slave/standby database server.

scp /tmp/db_file_backup.tar root@opensourcedbms_pg_slave:/tmp/

Standby server configuration:

step1:First stop server if it is running

#service postgresql-9.2 stop

step2:Rename the existing data directory.

mv /var/lib/pgsql/9.2/data/ /var/lib/pgsql/9.2/data.old

step3:Unzip master/primary server data snapshot file that is copied into this server.

tar xvfP /tmp/db_file_backup.tar

step4:Remove postmaster.pid so standby server does not see the primary server’s pid as its own.

rm -f /var/lib/pgsql/9.2/data/postmaster.pid

step5:Now edit configuration file and tweak hot_standby variable.

vi /var/lib/pgsql/9.2/data/postgresql.conf

hot_standby = on

step6:cp /usr/pgsql-9.2/share/recovery.conf.sample /var/lib/pgsql/9.2/data/recovery.conf

step7:Edit this recovery.conf file and update standby server settings:

vi /var/lib/pgsql/9.2/data/recovery.conf

standby_mode = on
primary_conninfo = 'host=192.168.1.4 port=5432 user=postgres password=postgres' ------Give the primary database IP address

step8:Update permissions on recovery.conf file

chown postgres.postgres /var/lib/pgsql/9.2/data/recovery.conf

step9: Start the postgresql service

service postgresql-9.2 start

Using Below queries check the replication status

select client_addr, state, sent_location, write_location,flush_location, replay_location from pg_stat_replication;

select * from pg_stat_replication ;

select pg_last_xlog_replay_location(); (standby side)

select pg_is_in_recovery();( to know the recovery mode)

To Know the log delay:

SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
END AS log_delay;

Read More