2017 – A year of learning !!
Today technology is changing at a very rapid speed and it’s very difficult to keep up to it but as said, it’s never too late to start something new. This year I plan to spend some time outside of Oracle and learn some new things.
I have started learning PostgreSQL and I want to take this platform to share my learning and to learn from others. As this is the start, it would very basic and hopefully with time, I will share more interesting things.
So, without wasting any more time, lets get started.
This blog is about PostgreSQL installation on MAC and a little more.
As HomeBrew is already running on my mac, I will use “Brew” to install PostgreSQL.
HomeBrew is a free and open-source software package management system that simplifies the installation of software on Apple’s macOS operating system.
You can read more about it on —
https://en.wikipedia.org/wiki/Homebrew_(package_management_software)
http://brew.sh/
Brew Installation is very simple.
bash-3.2$ ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
==> This script will install:
/usr/local/bin/brew
/usr/local/share/doc/homebrew
/usr/local/share/man/man1/brew.1
/usr/local/share/zsh/site-functions/_brew
/usr/local/etc/bash_completion.d/brew
/usr/local/Homebrew
Once installed, PostgreSQL installation is just one line command :-
bash-3.2$ brew --version
Homebrew 1.1.8
Homebrew/homebrew-core (git revision 3605; last commit 2017-01-23)
bash-3.2$
bash-3.2$
bash-3.2$ brew install postgres
==> Downloading https://homebrew.bintray.com/bottles/postgresql-9.6.1.el_capitan.bottle.tar.gz
######################################################################## 100.0%
==> Pouring postgresql-9.6.1.el_capitan.bottle.tar.gz
==> Using the sandbox
==> /usr/local/Cellar/postgresql/9.6.1/bin/initdb /usr/local/var/postgres
==> Caveats
If builds of PostgreSQL 9 are failing and you have version 8.x installed,
you may need to remove the previous version first. See:
https://github.com/Homebrew/homebrew/issues/2510
To migrate existing data from a previous major version (pre-9.0) of PostgreSQL, see:
https://www.postgresql.org/docs/9.6/static/upgrading.html
To migrate existing data from a previous minor version (9.0-9.5) of PostgreSQL, see:
https://www.postgresql.org/docs/9.6/static/pgupgrade.html
You will need your previous PostgreSQL installation from brew to perform `pg_upgrade`.
Do not run `brew cleanup postgresql` until you have performed the migration.
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
==> Summary
🍺 /usr/local/Cellar/postgresql/9.6.1: 3,242 files, 36.4M
bash-3.2$
Now, to start the PostgreSQL services
bash-3.2$ brew services start postgresql
==> Tapping homebrew/services
Cloning into '/usr/local/Homebrew/Library/Taps/homebrew/homebrew-services'...
remote: Counting objects: 10, done.
remote: Compressing objects: 100% (7/7), done.
remote: Total 10 (delta 0), reused 5 (delta 0), pack-reused 0
Unpacking objects: 100% (10/10), done.
Tapped 0 formulae (37 files, 50.7K)
==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)
bash-3.2$
bash-3.2$ ps -ef | grep postgres
1463626507 35179 1 0 7:38AM ?? 0:00.03 /usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres
1463626507 35182 35179 0 7:38AM ?? 0:00.00 postgres: checkpointer process
1463626507 35183 35179 0 7:38AM ?? 0:00.01 postgres: writer process
1463626507 35184 35179 0 7:38AM ?? 0:00.00 postgres: wal writer process
1463626507 35185 35179 0 7:38AM ?? 0:00.00 postgres: autovacuum launcher process
1463626507 35186 35179 0 7:38AM ?? 0:00.00 postgres: stats collector process
1463626507 35191 26530 0 7:38AM ttys004 0:00.00 grep postgres
bash-3.2$
bash-3.2$ postgres --version
postgres (PostgreSQL) 9.6.1
Few things to note here :-
1. The postgresql services can be stoped/started either using “brew services” or pg_ctl utility. “Brew services” will only be avaliable in case you have installed homebrew.
2. /usr/local/var/postgres –> Is the data directory. The location can be different depending on the OS. It contains configuration file, pid detail file, tablespaces and other details. I will post more on this in another blog.
3. The PID of the postgres process can be found using the ps program, or from the file postmaster.pid in the data directory.
4. PostgreSQL postmaster is the master server process. postmaster is a deprecated alias of postgres.
bash-3.2$ cd /usr/local/var/postgres
bash-3.2$ cat postmaster.pid
59448 --> PID
/usr/local/var/postgres --> Data Directory
1485658006
5432 --> Port
/tmp --> Unix socket
localhost
5432001 1310723
postgres=# \conninfo
You are connected to database "postgres" as user "prakanan" via socket in "/tmp" at port "5432".
5. After the service is up and running, you can connect to the database using psql, similar to sqlplus in Oracle.
bash-3.2$ psql
psql: FATAL: database "anand" does not exist
bash-3.2$
By default, psql tries to connect to dbname with the username its installed.
6. PostgreSQL comes with 3 default databases
i. template0
ii. template1
iii. postgres
The template0 and template1 databases are known as template databases. The teplate1 database can be changed to allow to created a localized template for any new db to create. The template0 exists so so that when you alter template1, you still have a pristine copy to fall back on.
By default, the new database will be created by cloning the standard system database template1.
The template0 database is normally marked datallowconn = false to prevent its modification.
If you have worked on Oracle 12c multitenant db, then you can think of template0 as CDB and template1 as PDB.
7. Two tablespaces are automatically created when the database cluster is initialized. The pg_global tablespace is used for shared system catalogs. The pg_default tablespace is the default tablespace of the template1 and template0 databases
8. As Oracle has Alert log, PostgreSQL has kind of similar logfile. In MAC installation, the location is /usr/local/var/log.
You can look at other installation options at
https://www.postgresql.org/download/
In the next blog post, I will show how to connect to database and different kind of start/stop mode.
