Multiple PostgreSQL instances on one server
9:34 AM1. Create new data directory for second instance.
[hostname_postgres ~]# mkdir /var/lib/pgsql/9.4/data2 [hostname_postgres ~]# chown -R postgres.postgres /var/lib/pgsql/9.4/data2
2. Create new start/stop/restart script
[hostname_postgres ~]# cp /etc/init.d/postgresql-9.4 /etc/init.d/postgresql2-9.4
[hostname_postgres ~]# vi /etc/init.d/postgresql2-9.4
# Set defaults for configuration variables
90 PGENGINE=/usr/pgsql-9.4/bin
91 PGPORT=5433
92 PGDATA=/var/lib/pgsql/9.4/data2
93 PGLOG=/var/lib/pgsql/9.4/pgstartup2.log
94 # Log file for pg_upgrade
95 PGUPLOG=/var/lib/pgsql/$PGMAJORVERSION/pgupgrade2.log
96
97 lockfile="/var/lock/subsys/${NAME}"
98 pidfile="/var/run/${NAME}.pid"
3. Make changes in configuration file.
[hostname_postgres ~]# vi /var/lib/pgsql/9.4/data2/postgresql.conf listen_addresses = '*' port = 5433 [hostname_postgres ~]# vi /var/lib/pgsql/9.4/data2/pg_hba.conf host all admin 192.168.1.2/24 md5
Initialize and start PostgreSQL service
[hostname_postgres ~]# service postgresql2-9.4 initdb [hostname_postgres ~]# service postgresql2-9.4 start
Login locally
[hostname_postgres ~]# su - postgres -bash-4.1$ psql -p5433 postgres=#create role admin login password '$0m3p@ssw0rd' superuser; postgres=#select * from pg_catalog.pg_user;
Login remotely
[hostname_postgres_remote ~]# psql -U admin -d postgres -h 10.182.74.45 -p5433 Password for user admin: psql (9.4.1) Type "help" for help. postgres=#
Test processes and connections
[hostname_postgres ~]# ps -ef | grep -i postmaster postgres 7049 1 0 May18 ? 00:00:00 /usr/pgsql-9.4/bin/postmaster -D /var/lib/pgsql/9.4/data postgres 15629 1 0 02:01 ? 00:00:00 /usr/pgsql-9.4/bin/postmaster -D /var/lib/pgsql/9.4/data2 root 16093 2307 0 02:03 pts/0 00:00:00 grep -i postmaster [hostname_postgres ~]# netstat -a | grep -i postgres tcp 0 0 *:postgres *:* LISTEN tcp 0 0 hostname_postgres:postgres hostname_postgres_remote:39899 ESTABLISHED tcp 0 0 *:postgres *:* LISTEN
0 comments