In the world of open-source ERP systems, ERPNext, powered by the Frappe framework, stands out for its flexibility and scalability. Traditionally, ERPNext has been closely associated with MariaDB as its default database. However, the growing demand for Postgres, known for its robustness and advanced features, has increased interest in integrating it with ERPNext. This guide walks you through setting up ERPNext/Frappe with Postgres, whether you’re working locally or remotely.

Understanding the Compatibility and Challenges of Integrating Postgres with ERPNext/Frappe

Before diving into the integration process, it’s essential to understand that ERPNext/Frappe was initially designed with MariaDB in mind. Integrating Postgres requires careful configuration and an awareness of potential compatibility issues. While Frappe does offer some level of support for Postgres, certain ERPNext features may function differently than they would with MariaDB. Nevertheless, the benefits of using Postgres, such as advanced indexing, scalability, and extensive JSON support, make it an attractive option for many developers.

Pre-requisites for Successful Integration

To ensure a smooth integration process, make sure you have the following prerequisites in place:

  • A working knowledge of ERPNext/Frappe: Familiarity with the Frappe framework and ERPNext is essential.
  • Postgres installed and configured: Ensure that Postgres is installed and running on your local machine or server.
  • Frappe-Bench installed: Frappe-Bench is the command-line utility that manages Frappe and ERPNext installations.
  • Basic understanding of Linux commands: Since most setups are done on Linux-based systems, familiarity with the command line is beneficial.

Ensuring Compatibility and Configuring Postgres for ERPNext/Frappe

To integrate Postgres with ERPNext, you’ll need to configure Postgres to work seamlessly with Frappe. This involves creating a dedicated Postgres user and database for ERPNext. Here’s how:

  1. Install Postgres:

    sudo apt update

sudo apt install postgresql postgresql-contrib

  1. Create a Postgres User and Database:

    sudo -i -u postgres

psql

CREATE USER frappe_user WITH PASSWORD ‘yourpassword’;

CREATE DATABASE erpnext_db OWNER frappe_user;

ALTER ROLE frappe_user SET client_encoding TO ‘utf8’;

ALTER ROLE frappe_user SET default_transaction_isolation TO ‘read committed’;

ALTER ROLE frappe_user SET timezone TO ‘UTC’;

\q

exit

Setting Up Frappe-Bench for ERPNext/Frappe

Frappe-Bench is a critical tool in managing ERPNext instances. Before proceeding with the Postgres integration, ensure that Frappe-Bench is installed on your machine:

  1. Install Frappe-Bench:

    sudo apt install python3-pip

sudo pip3 install frappe-bench

bench init –frappe-branch version-13 frappe-bench

cd frappe-bench

Installing and Configuring Frappe-Bench for Future ERPNext/Frappe Sites

Once Frappe-Bench is installed, you’ll need to configure it to work with Postgres for any new sites you create:

  1. Configure Frappe-Bench for Postgres: Open the common_site_config.json file located in the frappe-bench directory and add the following configuration:
    {

  “db_type”: “postgres”,

  “postgres_host”: “localhost”,

  “postgres_port”: “5432”

}

Configuring ERPNext/Frappe to Use Postgres

With Postgres configured, you can now modify the site configuration to use Postgres:

  1. Modify Site Configuration for Postgres Database Connection: When creating a new site, specify that you want to use Postgres:

    bench new-site yoursite.local –db-type postgres –db-host localhost –db-name erpnext_db –db-user frappe_user –db-password yourpassword

Step-by-Step Guide to Setting Up a New ERPNext/Frappe Site with Postgres

Follow these steps to set up a new ERPNext/Frappe site with Postgres as the backend:

  1. Create a New Site:

    bench new-site erpnext.local –db-type postgres

This command creates a new Frappe site configured to use Postgres.

  1. Install ERPNext on Your Site:
    bench get-app erpnext –branch version-13

bench –site erpnext.local install-app erpnext

  1. Adding ERPNext Application to Your ERPNext/Frappe Site: Now, you can add the ERPNext app to your new site:
    bench –site erpnext.local install-app erpnext

Finalizing the Setup and Starting ERPNext

Once the installation is complete, you can finalize the setup and start ERPNext:

  1. Start ERPNext:
    bench start
  2. Access ERPNext: Open your web browser and navigate to http://yoursite.local:8000 to access your ERPNext instance.

Conclusion

Integrating Postgres with ERPNext/Frappe offers significant benefits, particularly for organizations already relying on Postgres for other applications. Following this comprehensive guide, you can configure ERPNext/Frappe with Postgres for local and remote setups, ensuring a robust and scalable ERP solution.

References

Creating and connecting to a PostgreSQL DB instance

Connecting to a DB instance running the PostgreSQL database engine