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
orpython3
, 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:
1 2 | 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:
1 | 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:
1 | pip3 install cockroachdb |
- Execute the
cockroach node status
command to check the status of the nodes running CockroachDB. Use theps -ef | grep cockroach
orlsof -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:
1 | 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
How to create a CockroachDB database and grant permissions to users
First, open the SQL shell interface, as shown here:
1 | cockroach sql --insecure |
Now create a user, a database and give database permissions to the user using these SQL commands:
1 2 3 | 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:
1 | brew update && brew install postgresql |
Use the following command to start the service once the Homebrew installation has completed: PostgresSQL:
1 | 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:
1 | sudo apt-get update |
Next, install the PostgresSQL library from the apt-get
repository, as follows:
1 | sudo apt-get install postgresql |
Next, start the service with the following command:
1 | 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:
1 2 | 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:
1 | 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:
1 | psql -c 'SELECT version();' |
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:
1 | ps auxw | grep post |
Use the below PID command to kill a process:
1 | sudo kill -9 {PID} # replace {PID} with process number |
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:
1 2 | 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:
1 2 | 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:
1 | 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:
1 | ls -la |
As a precaution, once the files have been located, backup the data with the below command before deleting it:
1 | sudo cp postmaster.pid ~/Downloads |
Use the following rm
command to delete or remove the file:
1 | 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,
<p>If the [cc
escaped='true'
tab_size='4'
width='auto'
height='auto'
noborder='true'
nowrap='false'
inline='true'
]database does not exist
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:
1 | 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:
1 2 | sudo apt-get update sudo pip3 install --upgrade setuptools |
Then use apt-get
to install the psycopg2
library for Python 3, as shown here:
1 2 | 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:
1 | sudo python3 setup.py build_ext --pg-config |
Alternatively, try installing the binary package with PIP first, as shown here:
1 | 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:
1 | 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:
1 2 | 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:
1 | 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:
1 2 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | # 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) |
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!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started