Working with Postgres SQL in macOS

Install / upgrade Postgresql

smartechie-macos :~ $  brew <install | upgrade> 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:06

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 Postgresql 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

 

Trouble shooting

  1. Error
    1. smartechie-macos ~ $ pg_ctl -D /usr/local/var/postgres start && brew services start postgresql
      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

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

      2. Result
        1. smartechie-macos ~$ psql postgres
          psql (10.4, server 9.6.2)
          Type “help” for help.

          postgres=#

 

Advertisements

Create Superuser in HUE

[ec2-user@ip-123-45-67-890 ~]$ ls -ltr /usr/lib/hue/build/env/bin/hue

-rwxr-xr-x 1 root root 523 Sep 22 22:09 /usr/lib/hue/build/env/bin/hue

[ec2-user@ip-123-45-67-890 ~]$ sudo /usr/lib/hue/build/env/bin/hue createsuperuser

Username (leave blank to use ‘root’): sudhir

Email address: mail2sudhir.online@gmail.com

Password:

Password (again):

Superuser created successfully.

[ec2-user@ip-123-45-67-890 ~]$