https://DevOpsCloud.io -- Cloud Monk Losang Jinpa

Return to PostgreSQL - brew install postgresql, PostgreSQL Installation, Managing PostgreSQL on macOS, macOS Software

This is a tutorial for setting up PostgreSQL 12.2 on macOS.

If you want to run PostgreSQL on Windows instead, you will find guidance over here: How to setup PostgreSQL on Windows - https://www.robinwieruch.de/postgres-sql-windows-setup

Use Homebrew for installing and managing applications on macOS. It is installed using the following command in the macOS terminal:

/usr/bin/ruby -e “$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)”

The terminal runs through a series of installation operations, and will probably create folders in your local machine to accommodate Homebrews storage requirements. You can find more detailed instructions here (https://www.robinwieruch.de/developer-setup). After it's installed, update the Homebrew dependencies and install PostgreSQL on the command line:

• brew update
• brew install postgresql

=⇒ Pouring postgresql-12.2.catalina.bottle.tar.gz =⇒ 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 macOS background service you can just run:

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

Next, check your PostgreSQL version:

• postgres --version
• Response: postgres (PostgreSQL) 12.2

The command line results will show the version you have installed on your local machine. I recommend using the latest version of libraries and software whenever possible to avoid compatibility issues with client-side applications.

Now you can initialize the physical space on your hard-disk to allocate databases. To do this, create a default postgres database on the command line in case it didn't happen automatically:

With initdb:

• initdb /usr/local/var/postgres

You will see the error message: “initdb: directory ”/usr/local/var/postgres“ exists but is not empty” if the database was already created when you installed PostgreSQL. It means the folder where you are attempting to create a physical place for the database already has one. Either way, next you can move on to the next step.

When you connect to this physical database later, you will see an actual database which is called “postgres” as well. The postgres database is meant to be the default database for any third-party tools that you are using in combination with PostgreSQL. These tools attempt to make the default connection to this default database, so you shouldn't delete it.

Let's see next how you can interact with the actual database. Manually start and stop your Postgres database server with the following pg_ct commands:

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

• pg_ctl -D /usr/local/var/postgres stop

The terminal will confirm these operations with “server started” and “server stopped” feedback. You could also implement a script to start the server each time you boot up the machine, but I like to have control over when to start and stop my database server to avoid complications.

Next, let's go through the steps of setting up a database that can be used for one of your applications. Make sure the Postgre server is started first, then type these commands in the command line to create and remove a database:

createdb mydatabasename dropdb mydatabasename You can also connect to databases to execute SQL statements. Either use the psql command, or specify a database such as the default postgres database to connect:

psql mydatabasename The command leads you to the psql shell, which you can exit by typing CTRL + d. In the psql shell, you can create and drop databases as well:

CREATE DATABASE mydatabasename; DROP DATABASE mydatabasename; To list all your databases, you can type \list. Your will see any new databases listed, as well as two default databases that come with postgreSQL called template0 and template1. The templates should remain in your database list even if you aren't using them, as they may be useful later.“

\list - List all of your actual databases. \c mydatabasename - Connect to another database. \d - List the relations of your currently connected database. \d mytablename - Shows information for a specific table.

Fair Use Source: https://www.robinwieruch.de/postgres-sql-macos-setup