Postgresql Brew

Posted on  by admin

💚 pgctl -D /usr/local/var/postgres start waiting for server to start. 2021-01-17 17:03:03.686 EST 13710 FATAL: database files are incompatible with server 2021-01-17 17:03:03.686 EST 13710 DETAIL: The data directory was initialized by PostgreSQL version 12, which is not compatible with this version 13.1. $ brew install postgresql When you install Postgres, Homebrew will provide useful information in your Terminal that you should read. Homebrew also helpfully creates a default database cluster. You can confirm that if you see the following output. [email protected]: 7,380: Installs on Request (30 days) [email protected]: 7,376: Build Errors (30 days) [email protected]: 1: Installs (90 days) [email protected]: 24,289: Installs on Request (90 days) [email protected]: 24,241: Installs (365 days) [email protected]: 97,556: Installs on Request (365 days) [email protected]: 97,020.

This is a tutorial for setting up PostgreSQL on MacOS. You might be wondering why there's a need for another setup guide for Postgres in the first place, since there are a few across the web. I found many tutorials and guides on how to set it up, but found most of them to be unclear and outdated, based on old PostgreSQL versions. The checklist is not comprehensive, though, as it is used for several tutorials on my website, I keep it updated with the recent versions of PostgreSQL. If you spot any flaws in this guide, a comment below would be very helpful to keep it up to date for other developers.

If you want to run PostgreSQL on Windows instead, you will find guidance over here: How to setup PostgreSQL on Windows.

PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance. Postgres.app is a simple, native macOS app that runs in the menubar without the need of an installer. Open the app, and you have a PostgreSQL server ready and awaiting new connections. Close the app, and the server shuts down. PostgreSQL can also be installed on macOS using Homebrew. Please see the Homebrew documentation.

PostgreSQL Installation on MacOS

I recommend Homebrew for installing and managing applications on MacOS. It is installed using the following command in the MacOS terminal:

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. After it's installed, update the Homebrew dependencies and install PostgreSQL on the command line:

Next, check your PostgreSQL version:

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

How to create a physical PostgreSQL Database

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:

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.

How to start/stop a PostgreSQL Database

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

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.

How to create the actual PostgreSQL Database

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:

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:

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:

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.
Continue Reading: PostgreSQL with Sequelize in Express Tutorial

In this tutorial we will learn to install PostgreSQL database on Mac using Homebrew.

Prerequisite

It is assumed that you have Homebrew installed on your Mac.

If you don't have Homebrew installed on your Mac then open Terminal and run the following command.

You can visit Homebrew official website https://brew.sh to learn more about it.

Once you have Homebrew (a.k.a. brew) installed on your machine you can run the following command in the Terminal to check the version.

To update run the following command.

Alright, time to install PostgreSQL on Mac.

Install PostgreSQL using Homebrew

In Terminal run the following command to install PostgreSQL on Mac using Homebrew.

We can check the version of PostgreSQL using the psql command.

Start PostgreSQL

To start PostgreSQL run the following command in the Terminal.

We will get a similar output shown below.

Stop PostgreSQL

To stop PostgreSQL run the following command in the Terminal.

We will get a similar output.

Restart PostgreSQL

To restart PostgreSQL run the following command in the Terminal.

We will get a similar output as shown below.

Login to PostgreSQL database

By default we will get a database by the name postgres. So, to connect to it we will run the following command.

Brew Install Psql

We will see the following output.

List all the users

To list all the users we use the du command.

List all the databases

Postgresql Brew Types

To list all the databases run the l command.

Create a database

To create a database run the following command. In the given example mydb is the name of the database.

Connect to a database

To connect to a database use the c command.

List all the tables inside a database

To list all the tables inside a database we run the d command.

Note! If there is no table then we will get a prompt stating no relations found.

Postgresql

Postgresql Brew Maker

Let us go ahead and create a simple users table inside the mydb database and try the above command again.

Create table

In the following example we are creating a simple users table.

Now if we list the tables using the d command we will get the table.

How to exit from psql?

To exit or quit from psql type the q command.

Postgresql Brew Free

Alright, this brings us to the end of this tutorial. Hope you found it useful. Please share this tutorial if it was helpful. See you in the next tutorial. Have fun developing :-)