Skip to main content

Install PostgreSQL with EDB Installer

By adriatic​

image

Summary​

Introduction​

This article is written for all people who had problems following the Redwoods documentation on how to install the PostgreSQL database, using Brew.

If you installed PostgreSQL using Brew and have problems with this installation, start by uninstalling PostgreSQL using Brew. To do that, follow the process described in Completely uninstalling a Homebrew installation of PostgresQL (or any similar article). The assumption now is that you do not have PostgresQL installed on your computer - however, if you are trying to remove a failed installation being dscribed here, use the two scripts unistall-pgagent and uninstall-postgresql which are in the macOS library (~/Library/PostgreSQL/14) as shown below

image


Note: using these uninstallers may not be trivially simple, since these are shell scripts - not applications, so you will have to work in it a bit 😄.

Download the installer​

Fetch the installed from the PostgreSQL EDB (enterprise db) website as shown below:

image
Use version 14.4, for macOS


The downloaded file is postgresql-14.4-1-osx.dmg (size 303.2 MB).

image


Using the installer​

It should be installed using the information in the document Installation of PostgreSQL on Mac OS. For your convenience here is alse the link to PostgresQL tutorials.

Double-click on the postgresql-14.4-1-osx.dmg file in the Finder, will result (after supplying the needed authorization to macOS) with the first page of the Setup Wizard

image
PostgresQL Setup Wizard


  • accept the default installation directory (/Library/PostgreSQL/14)
  • accept the offer to install all four item (server, pgadmi, stack builder and command line tools)
  • accept the default data directory (/Library/PostgreSQL/14/data)
  • set the username and password for the postgres superuser (make sure to save this password somewhere safe). Note that the superuser is already set and yo cannot change it.
  • accept the port 5433
  • accept the default locale

These choices result with the below listed settings - and the installer starts its job.

Installation Directory: /Library/PostgreSQL/14
Server Installation Directory: /Library/PostgreSQL/14
Data Directory: /Library/PostgreSQL/14/data
Database Port: 5433
Database Superuser: postgres
Operating System Account: postgres
Database Service: postgresql-14
Command Line Tools Installation Directory: /Library/PostgreSQL/14
pgAdmin4 Installation Directory: /Library/PostgreSQL/14/pgAdmin 4
Stack Builder Installation Directory: /Library/PostgreSQL/14
Installation Log: /tmp/install-postgresql.log

image
Finished installation


Click on the Finish button, results with the prompt for Stack Builder installation. (see this page for explanation of this tool)

image
Start the Stack Builder


Be sure to select the local PostgreSQL instance before continuing the installation of the Stack Buider tool. Accept the default selections for the applications to be installed.

Create a database​

Start by launching the pgAdmin tool (it was added to the launchpad during the PostgreSQL installation)

image
Starting pgAmin tool


Then create a new postgresql database

image
Create new database for Redwood Blog application


Observe that this database's owner is "rwadmin" - this info will be used later in In order to save this database see the screenshot below:

image
Save this new database


Note that the reference button does not show on this screenshot. Click on that button results with

image
Save (backup) the new database


Verify the database installation correctness​

To verify the correct PostgresQL installation we will use a well-known Redwood Blog Application and modify it to use PostgreSQL database instead of the default SQLIte). We will do this in following several steps

1. Create the Example Repo application (aka Redwood Blog)​

In order to build this application we will start with the RedwoodJS Tutorial at this paragraph. Run the following commands in your terminal (the current directory should be similar to ~/dev/work/redwood/rw-community/redwood-tutorial-postgresql/) and observe that we named the clone redwood-tutorial-postgresql. That will allow us to first build this Redwood Tutorial App the standard way as explained in the setup section (just to verify that we have all needed piece needed to build this app from sources)

git clone https://github.com/redwoodjs/redwood-tutorial 
cd redwood-tutorial-postgresql
yarn install
yarn rw prisma migrate dev
yarn rw prisma db seed
yarn rw g secret

Note that the command yarn rw prisma db seed is implicitly included in the command yarn rw prisma migrate dev issue #64

2. Show where are the type and schema of the database defined​

Running the command yarn rw prisma migrate dev results with the following information to console:

Running Prisma CLI...
$ yarn prisma migrate dev --schema /Users/nik/dev/work/redwood/rw-community/redwood-tutorial-postgresql/api/db/schema.prisma

Prisma schema loaded from api/db/schema.prisma
Datasource "db": SQLite database "dev.db" at "file:./dev.db"

SQLite database dev.db created at file:./dev.db

Applying migration `20210222013102_init_database`
Applying migration `20220208231150_create_user`

The following migration(s) have been applied:

migrations/
  └─ 20210222013102_init_database/
    └─ migration.sql
  └─ 20220208231150_create_user/
    └─ migration.sql

Your database is now in sync with your schema.

This step created the database as a SQLite database at "file:./dev.db" (check Getting Dynamic section of the RedwoodjS Tutorial for details)

image
Everything related to database


3. Replace the SQLite with Postgresql database​

We already named the app to be redwood-tutorial-postgresql, so the app we are building will have the correct name. To switch the database we need to edit definition of the provider variable in the file schema.prisma to be:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

The format of the url (aka PostgreSQL connection URL) is:

postgresql://USER:PASSWORD@HOST:PORT/DATABASE

So, "DATABASE_URL" enviroment variable (for our locally installed database) is:

DATABASE_URL=postgresql://rwadmin:my-secret-password@localhost:5432/redwood-blog