Express setup for worlds most advanced PostgreSQL 9.3 server on a CentOS-6.5 x64 instance

Setup a PostgreSQL 9.3 database on CentOS 6.5 Server

postgresql

Worlds most advanced database is a killer punchline though.

Assuming you are a root user and looking for just the command that will get you through the installation.
Pre-Requisites
  - CentOS 6.5 x64 ISO minimal installation 
    Download Link: https://www.centos.org/download 
  - I prefer to install most of the dev dependencies beforehand via 
    $ sudo yum groupinstall 'Development Tools'

1. Create a User
   $ adduser postgres
   $ passwd postgres

2. Add this user to the sudoers list
   $ vim /etc/sudoers
   postgres ALL=(ALL)    ALL
 
3. Get the postgres repo for installation
   $ yum localinstall http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm
     yum localinstall https://yum.postgresql.org/9.3/redhat/rhel-7-x86_64/pgdg-redhat93-9.3-3.noarch.rpm
   $ yum list postgres*
   $ yum install postgresql93-server

4. Data folder is at the location
   $ ls /var/lib/pgsql/9.3/
  
5. Make this run as a service 
   $ service postgresql-9.3 initdb
     OR
     /usr/pgsql-9.3/bin/pg_ctl init -D /var/lib/pgsql/9.3/data/ -o --pwprompt -U postgres
     OR
     /usr/pgsql-9.3/bin/initdb --pgdata=/var/lib/pgsql/9.3/data/ -W -U postgres
   $ chkconfig postgresql-9.3 on
  
6. Control commands for the server
   $ service postgresql-9.3 start
     /usr/pgsql-9.3/bin/pg_ctl -D /var/lib/pgsql/9.3/data -l logfile start
   $ service postgresql-9.3 stop
     /usr/pgsql-9.3/bin/pg_ctl -D /var/lib/pgsql/9.3/data -l logfile stop
   $ service postgresql-9.3 restart
  
7. Change to user postgres for accessing the database through psql client
   $ su postgres
   $ psql

8. Modify the IPv4 access to this machine,
   by editing "/var/lib/pgsql/9.3/data/pg_hba.conf"

   host    all             all             127.0.0.1/32            md5
   host    all             all             192.168.1.0/24          md5
   host    all             all             0.0.0.0/0               ident

9. Modify the service host to listen for all external clients
   by editing "/var/lib/pgsql/9.3/data/postgresql.conf"

   listen_addresses = '*'

10. Update the iptables policy for database server access
   $ vim /etc/sysconfig/iptables
     -A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
   $ service iptables restart
   $ service iptables restart 
   
   Diagnostics for port forwarding. The DB server is unreachable hence switching the firewall OFF
   $ netstat -nlp | grep 5432
   $ sudo service iptables save
   $ sudo service iptables stop
   $ chkconfig iptables off

11. Update the password of the default user postgres. 
   This is done by logging in via the localhost psql client as above via default postgres user. 
   postgres# GRANT ALL privileges ON database postgres TO postgres; 
   postgres# ALTER USER postgres WITH password 'postgres'; 

   This will allow password based authentication via user postgres. 
   Verification can be done by attempting access as below: 
   $ psql -h 127.0.0.1 -U postgres -d postgres

Direct Reference : https://wiki.postgresql.org/wiki/YUM_Installation

Universal Database Manager: http://dbeaver.jkiss.org

In case the YUM repository is not set properly use the relevant EPEL repo 

## RHEL/CentOS 7 64-Bit ##
# wget http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-8.noarch.rpm
# rpm -ivh epel-release-7-5.noarch.rpm
## RHEL/CentOS 6 64-Bit ##
# wget http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
# rpm -ivh epel-release-6-8.noarch.rpm

 

_______________________________________________________________

 

A quick way to have a portable version running on windows locally is mentioned below,

Donwload: http://www.enterprisedb.com/products-services-training/pgbindownload

Extract the archive into a folder at “C:/Dev/pgsql”

Init: "C:/Dev/pgsql/bin\pg_ctl" -D  "C:/Dev/pgsql/data" initdb
Start: "C:/Dev/pgsql/bin\pg_ctl" -D "C:/Dev/pgsql/data" -l logfile start
Stop: "C:/Dev/pgsql/bin\pg_ctl" -D "C:/Dev/pgsql/data" -l logfile stop
Connect: "C:/Dev/pgsql/bin\psql" -U postgres

You can initially connect to database postgres, and later use the following to have a new user created with privilege on a db

CREATE USER postgres WITH PASSWORD 'postgres';
CREATE DATABASE sample;
GRANT ALL PRIVILEGES ON DATABASE "sample" to postgres;

Develop on Heroku #PaaS for overly simplified build, deployment, scaling and management of your NodeJS application. NodeJS is not the limit though

Heroku is a cloud platform as a service (PaaS) supporting several programming languages. The developers have added support for Java, Node.js, Scala, Clojure, Python and PHP in Heroku.

heroku-logo-300x93

It seamlessly takes care of version, build, package, deployment, scaling and management of the application. A developer can just focus on the code and not worry about the nightmares of release and deployment. This is the screenshot of how this deployment would look like.

HerokuSettingPage This is the settings page where you can see the domain and application configuration for this deployment.

HerokuActivityPage

This is the activity and log page which shows the events log at the PaaS system being used for the application

A.) Heroku Account

1.) Create a free account at https://id.heroku.com/login without using a credit card.

B.) Heroku publish a nodsjs application

 1.) Setup Heroku toolbelt in order to access the Heroku PaaS interface. This is the client that shall be used for any communication and interaction with the Heroku system.

 2.) Get some NodeJs application that would be deployed on Heroku.
     $ git clone https://github.com/vishwakarmarhl/ngboilerplate-heroku.git

 3.) Create a heroku app and make sure there is no nested subfolder structure hiding the app.js, package.json, Procfile files in your code.
     $ heroku create --app ngbp-rhl
       Creating ngbp-rhl... done, stack is cedar
       http://ngbp-rhl.herokuapp.com/ | git@heroku.com:ngbp-rhl.git
 
     $ heroku config:set BUILDPACK_URL=https://github.com/heroku/heroku-buildpack-nodejs#diet -a ngbp-rhl
       Setting config vars and restarting ngbp-rhl... done, v5
       BUILDPACK_URL: https://github.com/heroku/heroku-buildpack-nodejs#diet
4.) Push the repository to heroku for deployment
     $ git push heroku master
       Fetching repository, done.
       Counting objects: 5, done.
       Delta compression using up to 8 threads.
       Compressing objects: 100% (3/3), done.
       Writing objects: 100% (4/4), 522 bytes | 0 bytes/s, done.
       Total 4 (delta 1), reused 0 (delta 0)
       -----> Node.js app detected
       PRO TIP: Specify a node version in package.json
       See https://devcenter.heroku.com/articles/nodejs-support
       -----> Defaulting to latest stable node: 0.10.32
       -----> Downloading and installing node
       -----> Exporting config vars to environment
       -----> Installing dependencies
       -----> Cleaning up node-gyp and npm artifacts
       -----> Building runtime environment
       -----> Discovering process types
       Procfile declares types -> web
       -----> Compressing... done, 5.5MB
       -----> Launching... done, v4
       http://ngbp-rhl.herokuapp.com/ deployed to Heroku
       To git@heroku.com:ngbp-rhl.git
       c47ec59..2298a1a master -> master
 5.) Scale and manage the application using the Personal App in web console
    $ heroku ps:scale web=1
      Scaling dynos... done, now running web at 1:1X.
 
    $ heroku open
 
    $ heroku logs --tail
 6.) Now finally not all of us would like to push our apps in the blackhole and wait for it to crash before debugging. 
     Here is foreman that can help you initialize the app locally and test it out. 
     Foreman comes  as a heroku toolkit bundle.

    $ foreman start web
      18:56:31 web.1  | started with pid 13824
      18:56:31 web.1  | Listening on 5000

This is just a glimpse of the simplified workflow followed in order to bring up a nodejs application on heroku. Hoever the dependencies are to be managed by the application and verified for successful deployment based on the log and management console.