Working with Postgres SQL in macOS

Install / upgrade Postgresql
smartechie-macos :~ $ brew postgresql

Get installation details
smartechie-macos :~ $ brew info postgres

Result

postgresql: stable 10.4 (bottled), HEAD

Object-relational database system

https://www.postgresql.org/

Conflicts with:

postgres-xc (because postgresql and postgres-xc install the same binaries.)

/usr/local/Cellar/postgresql/9.6.3 (3,260 files, 36.6MB)

Poured from bottle on 2017-06-05 at 20:47:39

/usr/local/Cellar/postgresql/10.4 (3,389 files, 39.2MB) *

Poured from bottle on 2018-07-09 at 14:15:0

From: https://github.com/Homebrew/homebrew-core/blob/master/Formula/postgresql.rb

==> Dependencies

Required: openssl , readline 

Optional: python , python@2 ✘

==> Options

–with-dtrace

 Build with DTrace support

–with-python

 Enable PL/Python3 (incompatible with –with-python@2)

–with-python@2

 Enable PL/Python2

–without-perl

 Build without Perl support

–without-tcl

 Build without Tcl support

–HEAD

 Install HEAD version

==> Caveats

To migrate existing data from a previous major version of PostgreSQL run:

  brew postgresql-upgrade-database

To have launchd start postgresql now and restart at login:

  brew services start postgresql

Or, if you don’t want/need a background service you can just run:

  pg_ctl -D /usr/local/var/postgres start

Start service

If you notice from the info command output , its indicates the data directory for the DB with full start command. Just run that to start the postgresql.

To have launchd start postgresql now and restart at login:

smartechie-macos :~ $ brew services start postgresql

 

if you don’t want/need a background service you can just run:

smartechie-macos :~ $  pg_ctl -D /usr/local/var/postgres start

Sample commands and example

DDL Commands :

• create database
• create table
• drop table
• Alter table

 smartechie-macos ~ $ pg_ctl -D /usr/local/var/postgres start && brew services start postgres

Trouble shooting

Error :

 waiting for server to start….2018-07-16 11:31:59.391 PDT [57964] LOG: could not bind IPv4 address “127.0.0.1”: Address already in use
2018-07-16 11:31:59.391 PDT [57964] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2018-07-16 11:31:59.392 PDT [57964] LOG: could not bind IPv6 address “::1”: Address already in use
2018-07-16 11:31:59.392 PDT [57964] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2018-07-16 11:31:59.392 PDT [57964] WARNING: could not create listen socket for “localhost”
2018-07-16 11:31:59.392 PDT [57964] FATAL: could not create any TCP/IP sockets
2018-07-16 11:31:59.392 PDT [57964] LOG: database system is shut down
stopped waiting
pg_ctl: could not start server
Examine the log output.

spradhan-macos-2:/ sudhir.pradhan$ psql postgres
psql: FATAL: role “sudhir.pradhan” does not exist

1. 1. Solution

   1. 1. smartechie-macos ~ $ sudo -u postgres createuser -s $(whoami); createdb $(whoami)
         Password:Error

      1. Result

            smartechie-macos ~$ psql postgres
            psql (10.4, server 9.6.2)
            Type "help" for help.
            postgres=#

Refer the official link for more.