Introduction

1. Brew Install Psql
2. Homebrew Postgres
3. Brew Postgresql Start

I install Postgresql on my Ubuntu with: brew install postgres now I have: psql -version psql (PostgreSQL) 9.5.0 How can I start the service automatically? On my Mac with homebrew I can do it. Upgrading PostgreSQL 9.2 to 9.3 with Homebrew. What to do when PostgreSQL fails to start after a brew upgrade. Published September 23, 2013 Revised December 27, 2014: These instructions have also been tested for 9.3→9.4!

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. Hopefully you haven't run brew cleanup postgresql yet. Brew info gives all the relevant info, including if you still have the previous version and the fact you might need to run pgupgrade (shortened output below). This will tell you what version your db is on: cat /usr/local/var/postgres/PGVERSION.

After successfully installing postgresql@10 formulae via brew install postgresql@10, i then attempted to start postgresql using brew services start postgresql@10. $ brew services start postgresql@10 Successfully started `postgresql@10` (label: homebrew.mxcl.postgresql@10) however, no postgresql server is running, but the plist file was.

Django is a fast, high level developmental framework that allows for developing a web application in Python with a lot less hassle than some other frameworks. While Django automatically stores data in a SQLite database, this Django tutorial for postgresql will explain how to store data in Python with the Django framework using the PostgreSQL database cluster.

Prerequisites

• It must be confirmed whether the PostgreSQL database is installed on the local machine, as it will require the credentials that will be set to the Django application. Execute the sudo systemctl status postgresql command in a Systemd distro of Linux terminal to confirm the system is running, or just use the pg_ctl status command.

• For MacOS, start the Homebrew PostgreSQL service with the brew services start postgresql command and then execute the brew info postgres command.

• Python must be installed and working properly. Python 3 is recommended as Python 2.7 is being deprecated and losing support.

Enter the psql console

Execute the following command to enter the interactive PostgreSQL terminal that corresponds to the access of the PostgreSQL administrative user:

Now enter the sudo password or the root authentication for the device and press the -kbd-ENTER-/kbd- key.

When inside the PostgreSQL shell session, execute the following command to enter the console:

Create database and user

After entering the psql command-line console, a database and user must be created that can be set for the Django application. Execute the following command to create the database:

Now execute the following command to create a user login and password that will grant privileges to the newly created database:

Next, modify the parameters for the connection to the Django framework. This is needed to set up the operations for the value queries when a connection is established.

Execute the following ALTER ROLE SQL statement to modify the parameters:

NOTE: This operation will set the encoding to UTF-8, the default expected in Django framework.

Execute the following command to the default transaction isolation:

NOTE: This will set the default transaction isolation each time access is granted to make a query that avoids the transactions that are not committed.

Execute the following command to set the timezone:

NOTE: Django typically uses the UTC, by default, for the timezone.

Finally, execute the following command to grant the user access privileges to the database:

The results should resemble the following image:

Install Django in Virtual Environment

A virtual environment is used to manage the dependencies of different projects by creating a separation of Python isolation to the virtual environments.

Using the terminal, execute the following command to create a directory in the Django framework to store the project:

Now execute the following command to create a virtual environment that will store the Django project in Python.

NOTE: The venv command is now a built-in feature of Python since version 3.6. If using a Python version older than 3.6, the virtual environment package for Python must be installed and activated before installing the Django framework.

Execute the following command to activate the virtual environment:

With the venv activated, install the Django framework using the following pip3 command:

Now execute the following command to install the psycopg2 to allow configuring of PostgreSQL:

Start the Django project

Using the below command will allow for creating a folder to hold the codes within the project directory. Note that the manage.py is created after putting a dot at the end of the command to set the project correctly.

Execute the following command to start the Django project:

With the project now created, execute the following command to configure the project to set the PostgreSQL database credentials:

Look for the default configured database as SQLite and set it for the database credentials in PostgreSQL as follows:

Change the ALLOWED_HOSTS Python list of allowable domains, as shown below, and add the domain’s IP address or localhost for local development:

The results should resemble the following:

Test the Django application

With the configuration finished, the structures of data must be migrated to the database to test the application for the Django server.

Execute the following command to access the project directory:

Now add the following code to create a database structure:

Finally, execute the following code to create an administrative account for the Django application:

NOTE: A current admin account can be used to set up a superuser account for the Django application.

Now execute the following command to test the Django application and confirm it is working properly:

When the system displays a message that the application is working, add /admin in the URL and enter the previously set up username and password.

Conclusion

This Django tutorial for postgresql explained how to store data in Python with the Django framework using PostgreSQL. The tutorial explained how to create a database and user, grant the user access to the database, install Django in a virtual environment and activate the virtual environment. The Django tutorial for postgresql also explained how to start the Django project, create an administrative account and finally test the Django application. Remember that the virtual environment package for Python must be installed and activated separately, before installing the Django framework, if using a version of Python prior to 3.6.

This post was originally published as a Gist which I since forgotabout and accidentally rediscovered recently when the need arose toresolve a similar accidental upgrade. I felt like it deserved tolive somewhere where there is a chance I might update it.

Brew Install Psql

Update

In Homebrew 1.5 and above, there’s a new process to upgrade yourPostgres database across major versions using the brew postgresql-upgrade-databasecommand which was recently added by Mike McQuaid.

This is a fantastic one-step improvement over the lengthy guide you’llsee below, but since this new command isn’t foolproof (it will fail ifyou haven’t killed all processes connected to your database) it seemslike a good idea to still understand the way it works behind the scenes.

I’ve learned a cool new trick from Homebrew’s upgrade command however and youcan find it in the last section of this updated post.

Accidental Homebrew Upgrades

This guide assumes that you recently ran brew upgrade postgresql anddiscovered to your dismay that you accidentally bumped from one majorversion to another: say 9.3.x to 9.4.x. Yes, that is a major versionbump in PG land.

Some of the common signs of this issue are the following statements inyour logs:

No need to panic. Your data should be fine. While you’ve upgraded the Postgreslibrary, your existing databases have not been removed. They simply need to beupgraded to run with the new library.

Important Caveat for Non-Incremental Upgrades

The process I describe below will work for incrementalversion updates: for example 9.5 to 9.6. But it will probably not workif you jump versions: for example 9.5 to 10.0.

In order to upgrade properly you will first need to downgrade thePostgres version in Homebrew to the version directly after the one ofyour data directory.

How do you figure out what version your data directory uses? Ask it.

There we go. And what is your currently activated Postgres version inHomebrew?

Good. If you find yourself one step further than you should be, sayon 10.0 like I was. You can do the following to step back down to9.6:

Couldn’t you use this to go back to Postgres 9.5? Yes, you could. Butit’s never a bad idea to stay up to date with newer versions, so whynot use the opportunity to be less worried about upgrades?

Now, check the active version of Postgres with:

Checking Versions

You can check all the versions of postgres that are installed inHomebrew with:

In this case you can see that I don’t have Postgres 9.6 installed in theusual postgres formula, since I had to specifically install that versionin order to be able to upgrade from 9.5 withbrew install postgresql@9.6.

So there are 5 different versions of PostgreSQL installed on my machinein different subfolders. We can find out where those are with:

As you can see I have my old versions installed in/usr/local/Cellar/postgresql/. And what about this new postgres@9.6?

That one’s in a standalone directory:

One final check to make sure we have the correct versions of thePostgres commands running:

This means that the pg_upgrade binary we’ll be using is also the newone. But let’s not assume:

Good good.

Mise en place

The directory where your actual database data is stored is differentfrom the one where your PostgreSQL binaries are installed. Homebrewinstalls the data directory in /usr/local/var/postgres/ and doesn’ttouch that data folder when you upgrade from one version to the next.This is a good thing because if Homebrew tried to install a brand newdatabase (with initdb) it could squash all your existing data.

First I recommend moving your existing data to a directory with adifferent name:

Now that the old data directory has been “moved”, you can safely createa brand new clean database:

Your output will be slightly different. For one, the user won’t beolivierlacan but whatever your system user is. You can find that outeasily with whoami.

Interesting to note, but if you use PostgreSQL with Rails. This meansthere will be no password on your development database, so you can skipthe password field in database.yml or leave it *completely empty.

Shutting Things Down

First we have to make sure both database servers are not running when wedo the upgrade:

If you get the following message it’s possible that you have PG inlaunchctl which may prevent you from stopping the server:

In that case let’s remove PG from launchctl for now, you can add it backlater by following the instructions given on brew info postgres:

Then try stopping the server again:

This would be good news:

If you’re using a recent version of Homebrew (as you should), then it’slikely you’re using Homebrew Services instead of launchctl to managepersistent processes like postgres. So use this instead:

While Homebrew Services should do the job of stopping postgres, it’s notguaranteed that you don’t have a server process connected to postgresand locking it’s pid (process ID) file. It’s always a good idea tocheck for that file which may exist at/usr/local/var/postgres/postmaster.pid. If it does, remove it withrm /usr/local/var/postgres/postmaster.pid and you’ll save yourselfsome surprises later on.

Upgrading

I’ve based this article on the fairly detailed pg_upgrade guide in thePostgreSQL documentation. If you have specific needs (very largedata directory for example), you should consult it too. This sectionis also not specific to a macOS Homebrew install of Postgres since ituses the same commands you should be able to use on other operatingsystems like Linux.

Assuming you’re dealing with the same version numbers I’m dealing with(you probably aren’t, so change them when running this on your machine),this is what the pg_upgrade command should look like when you run it:

Lowercase flags (-b and -d) are for old binary and datadirectories respectively. Their uppercase counterparts are for their newequivalents.

I really dislike short form flags because they make self-descriptive discoveryvery difficult so here is the alternative with long-form flags:

How nice is this?

You should see the following output immediately if the upgrade processis starting:

You’re done!

Restarting

You manually shut down PG during this upgrade so now it won’t be runningunless you follow the (old) brew info postgres instructions:

With Homebrew Services, the operation is even simpler:

Once PG is running you could run the optimization script recommended bypg_upgrade. It was created in whichever directory you ran pg_upgradein, and you can run it with:

Troubleshooting

You may encounter the following error:

This means you have at least one PG server running. So go back to thebeginning of this section and make real sure you shut down all yourservers.

You can also check this pg_upgrade_server.log file to be certain ofthe issue. It should be located in/usr/local/var/log/pg_upgrade_server.log but you can always use thefind command to locate it:

When using brew postgresql-upgrade-database, this log should containthe reason the upgrade process failed as well as the actual commandused, which will be very useful for you to restart the upgrade processmanually.

I’ve found that running brew postgresql-upgrade-database again afteran initial failed attempt usually results in an error message sayingthat the data was already upgraded when it’s not actually true or atleast the process hasn’t fully completed.

You can see how this script works by checking its source code. Itseems like it’s comparing the Homebrew-installed version to theversion of the currently active Postgres database inside of the /var/postgresdata directory. It references the PostgreSQL documentation upgrade articleas well. The only notable difference with the instructions I’ve listed aboveis the use of the -j flag, which defines how many threads or processes thepg_upgrade command will use. Homebrew sets that number toHardware::CPU.cores.to_s which would be 2, 4, 6 or more CPU cores your machinemay have to handle CPU-intensive tasks like these.

However I’d recommend not doing this since you may have operations alreadyusing some CPU on your machine. My best tip is to use one less core than yourtotal, which I learned from this excellent Thoughbot blog post on parallelRubyGem install configuration for Bundler:

Homebrew Postgres

So here’s the final command in its full glory for all of you who skippedstraight to the bottom of this post:

Brew Postgresql Start

Safe upgrades!