Setup postgresql database on OSX

Install postgres on OSX

$ brew install postgres

$ pg_ctl –version

pg_ctl (PostgreSQL) 9.6.2

Now create a folder name h2odb as below

$ pwd

/Users/avkashchauhan/tools/postgres

$ mkdir h2odb

Now initialize the database into h2odb as below:

$ initdb h2odb 

The files belonging to this database system will be owned by user "avkashchauhan".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory h2odb ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

pg_ctl -D h2odb -l logfile start

Now visit inside h2odb folder and edit pg_hba.conf by added the following at the end of the file:

$ vi pg_hba.conf

Add/append the following in pg_hba.conf where h2odb is database name and avkash is the database user name:

local h2odb avkash md5
host h2odb avkash 127.0.0.1/32 md5
local all all md5
host all all ::1/32 md5

Start postgres with h2odb as database (You must be outside h2odb folder or add full path to it):

$ pg_ctl -D h2odb -l logfile start

Now run the following command to setup user avkash with password avkash for the database h2odb as below:

$ psql postgres -c “create user avkash PASSWORD ‘avkash'”

$ psql postgres -c “create database h2odb with owner avkash”

$ psql postgres -c “alter role avkash superuser”

$ psql h2odb -U avkash -c “select version();”

Now you can access the h2odb as below where parameters are “-U  user_name data_base_name” :

$ psql -U avkash h2odb

Above command will give you access to postgres shell where you can run command as:

> \l  (list databases) 
> \dt (list tables for the selected database)
> \?  (Get Help)
> select * from table_name;

To check if postgres is up and running:

$ ps -ef | grep postgres

Stop postgres with h2odb as database

$ pg_ctl -D h2odb -l logfile stop

Thats all!! Enjoy it..

 

 

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