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:

• The PIP package manager for Python must also be installed. Use the following command to upgrade PIP for Python 3:

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:

• 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:

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

How to create a CockroachDB database and grant permissions to users

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

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

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:

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

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:

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

Next, start the service with the following command:

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:

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:

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:

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

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:

Use the below PID command to kill a process:

How to fix a ‘psql: could not connect to server

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.

Stop and start the postgres service in a UNIX terminal with the following command:

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

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:

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:

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

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

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:

1	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:

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:

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

How to troubleshooting psycopg2 installation issues

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

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

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:

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

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:

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:

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:

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.