How to Install and Setup the Python Psycopg2 Driver for CockroachDB

Introduction

This tutorial will explain how to setup psycopg CockroachDB and install and setup the Python Psycopg2 Driver for CockroachDB. The psycopg2 library is designed specifically as a Python driver for CockroachDB and Psycopg is constructed to be dependent on the PostgresSQL relational database management system. While the CockroachDB and the Psycopg driver will work with Python 2, it is strongly recommended to use a Python 3.x version and its package manager command pip3 instead of Python 2.7 as Python 2.x is deprecated and scheduled to lose support by end of 2019.

Prerequisites to running Psycopg2 with CockroachDB

  • A rudimentary understanding of Python and its syntax. This will aid the use of libraries and wrappers, such as pg_query, that can iterate and parse PostgreSQL and Cockroach data.

  • Python version 2.7 or above must be be properly installed and working on the machine that will run the CockroachDB server. Confirm this by opening a terminal or command prompt window, typing python2 or python3, depending on the version being checked, and then press “Return” to confirm access to a Python interpreter.

How to upgrade the pip3 package manager

Using the PIP package manager is the simplest way to install Python packages. If pip3 is not already installed, do so before proceeding with this tutorial.

  • Use the following commands to install the setuptools for PIP3:
sudo pip3 install --upgrade setuptools
pip3 install -U setuptools
  • The PIP package manager for Python must also be installed. Use the following command to upgrade PIP for Python 3:
pip3 install --upgrade pip

How to install CockroachDB Python and start the cluster

  • The CockroachDB server must be installed and running on the server that will be using the psycopg2 driver. If it is not already installed, visit the download’s page to obtain the latest stable version of CockroachDB, or use PIP to install it, as shown here:
pip3 install cockroachdb
  • Execute the cockroach node status command to check the status of the nodes running CockroachDB. Use the ps -ef | grep cockroach or lsof -n -i4TCP:26257 commands to determine if any processes of CockroachDB are running.

For development and debugging purposes only, execute the following command to start an insecure, local cluster:

cockroach start --insecure --listen-addr=localhost

Navigate to http://localhost:8080 in a browser tab to interact with the CockroachDB cluster running via localhost.

Image of CockroachDB UI Admin Interface running on port 8080

Screenshot of the CockroachDB UI Admin cluster overview GUI interface on port 8080 in a browser tab

How to create a CockroachDB database and grant permissions to users

First, open the SQL shell interface, as shown here:

cockroach sql --insecure

Now create a user, a database and give database permissions to the user using these SQL commands:

CREATE USER IF NOT EXISTS objectrocket;
CREATE DATABASE test;
GRANT ALL ON DATABASE test TO objectrocket;

Type q and press Return to exit the shell interface.

How to Install PostgresSQL to Allow the Psycopg2 Python Driver to Function

Psycopg is a database adapter, written in Python, for the PostgreSQL object-relational database. Postgres must be properly installed and configured in the system’s OS PATH for psycopg2 to function correctly.

How to install PostgresSQL for macOS using Homebrew

Use the following Homebrew brew command to install PostgresSQL on a macOS computer:

brew update && brew install postgresql

Use the following command to start the service once the Homebrew installation has completed: PostgresSQL:

brew services start postgresql

How to install PostgresSQL on the Debian-based distro of Linux

If the server is a Debian-based distribution of Linux using the APT repository, update the repository packages before installing PostgresSQL, making sure to have sudo access, as follows:

sudo apt-get update

Next, install the PostgresSQL library from the apt-get repository, as follows:

sudo apt-get install postgresql

Next, start the service with the following command:

sudo service postgresql start

Confirm that PostgresSQL is properly installed and configured

A new database should now be created using the system’s username.

Enter the PostgreSQL shell-like interactive terminal using the psql command followed by two instances of the username; one instance for the database name and one instance for the username that has permissions to access the database, as follows:

psql {DATABASE_NAME} {USER_NAME}
# e.g. psql john john

NOTE Type q to exit the command interface for psql.

If it returns a version number, such as psql (11.3), as it loads the interactive terminal, or if it returns a Psql error, then the PostgresSQL is installed correctly.

Alternatively, the Postgres configuration settings may be accessed directly with the following command:

pg_config

Besides the version number, the above command should also return installation information and the directory where it is installed.

Execute the below command in a terminal window for psql to return its version number:

psql -c 'SELECT version();'

Press the CTRL+C keys to stop PostgreSQL and exit, as show here:

Screenshot of a terminal in macOS getting the PostgresSQL psql version

How to Troubleshoot the PostgreSQL Server

Look for the processes of PostgresSQL that are running if the program is not working correctly. Use the grep command to locate any processes with post in the name, as show here:

ps auxw | grep post

Use the below PID command to kill a process:

sudo kill -9 {PID} # replace {PID} with process number

How to fix a psql: <span>could not connect to server</span></h3>
<p>There is usually an issue with the server when this occurs, typically meaning the Postgres process didn't shut down properly. First, reboot the machine or server running Postgres. If that doesn't correct the error then try stopping and starting the PostgreSQL process, as show below. </p>
<h4 id='stop+and+start+the+%5Bcc+%0A%09%09%09escaped%3D%27true%27+%0A%09%09%09tab_size%3D%274%27+%0A%09%09%09width%3D%27auto%27+%0A%09%09%09height%3D%27auto%27+%0A%09%09%09noborder%3D%27true%27+%0A%09%09%09nowrap%3D%27false%27+%0A%09%09%09inline%3D%27true%27%0A%09%09%5Dpostgres%5B%2Fcc%5D+service+in+a+unix+terminal+with+the+following+command%3A'>Stop and start the [cc
            escaped='true'
            tab_size='4'
            width='auto'
            height='auto'
            noborder='true'
            nowrap='false'
            inline='true'
        ]postgres
service in a UNIX terminal with the following command:

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

Stop and start the postgres service on a macOS Homebrew installation with the following command:

brew services stop postgresql
brew services start postgresql

How to delete the postmaster.pid file and restart the server

WARNING: This method should only be used as a last resort, as it has the potentially to delete or damage all data stored with Postgres. To minimize the risk of losing data, make sure that all instances of postgres are shut down before attempting this. Use the following ps command to confirm no processes are running:

ps auxw | grep post

The Postgres server is supposed to delete the postmaster.pid file upon startup if the state of the server is invalid. However, it won’t do so if the process didn’t shutdown or start properly.

Depending on the OS and installed version of PostgresSQL, the file should be located in one of the following directories, or one of the sub-directories:

  • /usr/local/var/postgres/
  • /Users/{USER_NAME}/Library/Application Support/Postgres/
  • /usr/local/Cellar/postgresql/{VERSION}/bin

Go into the directory and list all of the files, including the hidden ones, with the following commands:

ls -la

As a precaution, once the files have been located, backup the data with the below command before deleting it:

sudo cp postmaster.pid ~/Downloads

Use the following rm command to delete or remove the file:

sudo rm postmaster.pid

After deleting the file, try restarting the PostgresSQL server or application. It may be necessary to also restart the server or machine.

How to fix the psql: <span>FATAL</span></h3>
<p>If the [cc
            escaped='true'
            tab_size='4'
            width='auto'
            height='auto'
            noborder='true'
            nowrap='false'
            inline='true'
        ]database does not exist
error occurs when trying to enter PostgresSQL using psql command,

try recreating the user database with the following command:

createdb -h localhost

How to install the Psycopg library on the server running CockroachDB

If PostgresSQL and the PIP3 package manager for Python are properly installed, just run the following command to install the psycopg library:

pip3 install psycopg2

Alternatively, visit the Psycopg download’s page to obtain the latest stable tar.gz package release.

How to installing

psycopg2 on Linux using apt-get

Prior to installing the package libraries for Psycopg, update the APT repository and install the setuptools with the following command:

sudo apt-get update
sudo pip3 install --upgrade setuptools

Then use apt-get to install the psycopg2 library for Python 3, as shown here:

sudo apt install libpq-dev python3-dev
sudo apt-get install python3-psycopg2

How to troubleshooting psycopg2 installation issues

If a build or other error occurs, try building the source packages with the following command:

sudo python3 setup.py build_ext --pg-config

Alternatively, try installing the binary package with PIP first, as shown here:

sudo pip3 install psycopg2-binary

How to confirm the pg_config for PostgreSQL is working and its PATH is properly set

If the service is still won’t work properly, try to export the working path for PostgreSQL’s pg_config service, using the find command as follows:

sudo find / -name "pg_config" -print

Once the path is located, start the super user mode and then export the system path for pg_config using the following command:

sudo su
export PATH=/usr/path/to/pg_config

NOTE: Make sure to replace /usr/path/to/pg_config with the correct working path. The proper path may depend on the OS and PostgreSQL installation method.

Press the CTRL+D keys to exit su mode.

How to build the psycopg2 driver from source using Python 3

Execute the following command:

python3 setup.py install

How to correct the installation error pg_config executable not found

An error such as pg_config is required to build psycopg2 from source or something similar, may display. If so, try installing psycopg2 again with sudo privileges.

How to import Psycopg2 into a Python script or interpreter

At this point there should be not be anImportError raised when entering a Python interpreter, opening an instance of the IDLE environment for Python 3, such asidle3 in a terminal, or importing the library.

How to import the psycopg2 library in Python and create a connection

Import the psycopg2 library and create a new connection instance by passing some values to the dbname and user parameters. The default for both values should be the system’s username. Type whoami into a UNIX terminal to find the system username, as show here:

import psycopg2
connection = psycopg2.connect("host=localhost dbname={USER_NAME} user={USER_NAME}")

If the Postgres server is running, and the correct dbname and user parameters were passed into the connect method’s string, then Psycopg should return a connection object as a response, as shown here:

How to Connect the Python Driver with CockroachDB

First, create a new instance of the Python interpreter, or a new script. Then import the library, but this time create a connection instance of the psycopg2 library that connects to the CockroachDB instead.

The default port for CockroachDB is 26257. This should suffice for the port parameters, unless the DB was configured differently. Make certain to enter the correct credentials and database name into the connect() method parameters, as shown here:

# import the psycopg2 driver
import psycopg2

# Connect to the "bank" database.
conn = psycopg2.connect(
database='test',
user='objectrocket',
sslmode='disable',
port=26257,
host='localhost'
)

# print the connection
print ("CockroachDB connection:", conn)

Screenshot of IDLE Python making a connection to a CockroachDB database using psycopg2

Conclusion

This tutorial demonstrated how to install psycopg Python, specifically PostgresSQL to allow the Psycopg2 Python Driver to function. The tutorial also demonstrated how to install the CockroachDB and start the cluster and how to upgrade the pip3 package manager. Installing psycopg2 and PostgreSQL and using them to connect Python to a CockroachDB database requires Python version 2.7 or higher. However, Python 3.x and its package manager command pip3 should be used instead as Python 2 is scheduled to lose support by end of 2020. After installing the psycopg driver for CockroachDB and the setup of psycopg CockroachDB is completed, confirm that PostgresSQL has been properly installed and configured.

Pilot the ObjectRocket platform free for 30 Days

It's easy to get started. Imagine the time you'll save by not worrying about database management. Let's do this!

PILOT FREE FOR 30 DAYS

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.