Connect Prestashop PostgreSQL: 2 Easy Methods

In recent years eCommerce has seen a large increase in the number of users, and this growth has only been exponential. Fueled by the pandemic, the survey shows that 43% of sales have been through eCommerce platforms and this number is to grow. Creating an eCommerce website is important for a company to be able to sell its products. Prestashop is a free-to-use solution that helps in creating and hosting eCommerce websites. It has the provision to choose various different database servers to be able to store all the information and it is very easy to combine with Postgres.

PostgreSQL is a relational database platform that provides more support when compared to other SQL platforms. By Connection PrestaShop PostgreSQL you can efficiently store the data and perform queries and analytics on it, even under heavy loads.

In this article, you will learn more about Prestashop, and Postgres and also find a step-by-step guide to connecting PrestaShop PostgreSQL together. You will also find different methods that can be used to connect PrestaShop PostgreSQL. 

What is PrestaShop?

Prestashop is a free-to-use platform that is open source and has a wide range of support. It has a feature-rich suite that has many out-of-box features that makes it a very useful platform. It is based on PHP, which makes it highly compatible with web pages. It provides tools that help merchants build comprehensive eCommerce solutions that also have high functionality due to the incorporation of PHP into the code. 

It is quite a popular platform with over 270,000 stores hosted on the platform spread over 65+ countries. It is trusted by about 250+ agencies and has a constant improvement which is actively done by developers which aid its growth and stay up-to-date and sometimes even ahead of its competition. Prestashop’s business model is to sell the services to commercial partners and increase the customer base.

Top features of PrestaShop.

eCommerce Builder

PrestaShop provides an interface that is similar to that of WordPress. This interface consists of parts like development sections, navigation panels, product listings that are incredibly detailed, and even search options.

eMarketing strategy

PrestaShop has the provision of a product catalog that can be integrated with SEO tools. This helps in cataloging all the promotions, coupons, email integrations, etc.

Free & Open Source

PrestaShop is an open-source platform that provides all the features for free. It also enables users to create a customizable website. The eCommerce website development is made more manageable and easy to develop.

Marketing

The PrestaShop platform allows its users the option to add custom functionality and features to make it exclusive. It also allows the inclusion of free shipping, discounts, and other features.

Easy Customization

PrestaShop has a provision for modules that can be added to the website so that it can be customized. It has more than 5000 options to be added.

Payment Gateways

PrestaShop has the support for all the payment gateways like Amazon pay, PayPal, and discover global. It has the provision to add about 250+ providers as add-ons.

Ease Of Use

PrestaShop uses a more technical background, but PrestaShop provides the demo for setting up the platform faster. It is available on the desktop, mobile, and tablet. Installing PrestaShop on your system is a breeze which makes it easy to use.

Integrations

PrestaShop has the availability of various themes and add-ons that provides support for integrations with third-party agents. This enables the store to offer more premium services.  

Shopping Cart

It provides the option to connect with payment providers, shipping carriers, and many more to meet the needs of consumers.

International

PrestaShop is available in more than 65 language translations for your eStore. Its cloud administrative dashboard is available in eight languages, Italian, Spanish, English, Portuguese, French, Polish, and Dutch. 

What is PostgreSQL?

Postgres is a shorter name for PostgreSQL. It is a free-to-use relational database management platform that is open source. It is SQL compliant and is highly extensible. It was launched on July 8, 1996, in a lab at the University of California. It was released to replace the Ignes database which was getting old. Postgres uses more traditional structuring when compared to MongoDB, where it uses traditional tables to store data.

It follows the monolithic methodology where all the components work in a united manner, that follows symmetry. It is based on the c Language, hence has a lot of community support. Being an open-source solution also helps in this cause of having constant updates. Postgres is used in the industries like healthcare, banking, manufacturing, and many more. 

Key Features of PostgreSQL

Postgres is a feature-rich solution that makes it a very popular RDMS. Some key features are:

• Different data types and various kinds of document formats are supported by Postgres.
• Since Postgres utilizes monolithic architecture, all the components work in automation.
• It provides a secure solution which makes it a good choice for banking systems that perform risk management.
• Since the architecture is traditional, the storage becomes reliable as it has lesser complexities. Also, it has fail-safes that help avoid data losses. 
• It is a feature-rich solution that is free to use and the most important thing, it is open source.
• Its scalability and processing power depending on the machine it is running on.
• It is ACID-compliant and runs on all major operating systems.

Steps for PrestaShop PostgreSQL Connection

Method 1: Simplify PrestaShop PostgreSQL By Eliminating The Need For Code using Hevo

Prerequisites

• ALL the credentials for the Prestashop are required. 
• MySQL 5.6v
• Hevo’s IP needs to be permitted (whitelist)
• SELECT privilege in the database is needed for PrestaShop PostgreSQL.
  

Step 1: Grant Privileges to the User

The SELECT privilege can be granted to the user using the following steps

• Log in to your MySQL database
• SELECT privilege can be granted to the database user using the following command:
  GRANT SELECT ON *.* TO ‘username’; # to grant privileges to a user with <username> as username

Step 1:Specify MySQL Connection Settings:

Configure your PrestaShop via MySQL Source page for PrestaShop PostgreSQL:

• Pipeline Name: This is the name that will be set to your pipeline. 
• Database Host: The Host Address for MySQL host.
• Database Port: This is the port number on which the MySQL server listens. Default value: 3306.
• Database User: The users that are authenticated to read tables in the database.
• Database Password: This is the password used by the database user. 
• Database Name: This is the name of the database from which you want to load the data
• Click TEST & CONTINUE to proceed with setting up the Destination for PrestaShop PostgreSQL.

Step 2: Set Postgres as Destination

Prerequisites

• For Postgres, you need CREATE and USAGE scheme privileges. 

Step 1: Add Destination

Do one of the following:

• After the source is selected, click on ADD DESTINATION. 
• Go to the Asset palette and click on the destinations. In the Destinations, the list view clicks on the + Create button.

Step 2: Select the Destination Type

• When you chose the first option in the previous step an Add destination page will open, select PostgreSQL.
• You can also search destination type to search for Postgres.

Step 3: Configure PostgreSQL Connection Settings

Configure your PostgreSQL Destination page for PrestaShop PostgreSQL:

• Destination Name: This name will be set to your destination.
• Database Host: This parameter will hold the Postgres IP Address
• Database Port: This parameter defines the Postgres listen to connections. Default value: 5432
• Database User: This is a non-admin user that will use Postgres
• Database Password: This is the password that the user will use to access Postgres.
• Database Name: This parameter stores the name that acts as the destination database for data to be stored. 
• Database Schema: This is the name of the database schema at the destination (Default value: public)

Step 4: Test Connection

After filling in the details, click on TEST CONNECTION to test connectivity to the Destination Postgres server.

Step 5: Save Connection

Once the test is successful, save the connection by clicking on SAVE DESTINATION.

Now the PrestaShop PostgreSQL Connection is complete

Method 2: Using AWS EC2 instance to connect PrestaShop PostgreSQL

Step 1: launch a new AWS EC2 instance for Prestashop

• In your AWS console, launch a  new EC2 Instance. For this example, we will use Ubuntu 14.04 as OS. 

• Now select the version of EC2 of your choice. 

• Now select the network for the EC2 instance, the subnet and its mask, and the IAM role. This role would be helpful later.

• Now define the size of the server. We are setting up s3 bucket for storage so that EC2 server does not require much disk space. For this example 10 GB is adequate. 

• Now the security groups need to be attached to the EC2 instance. This will allow the nodes to connect to the internet and its VPC securely. In this example we will you two groups:
  • Web Basic: access to the internet. HTTP, HTTPS, SSH.
  • VPC-XXX-XXX: gives access to the nodes — databases, S3 buckets, etc. — within the same VPC.

Basic Security Group

Note: The security group id and source need to be the same. 

• We need to assign an SSH key that can be attached to the server or else create a new one. This will enable an SSh connection for the server.
• EC2 server should be up and running.

Step 2: Configure the Ubuntu server for Prestashop

• Enter the SSh into EC2 instance.
• If a invalid locale warning pops up it can be fixed by just adding the missing value:

locales config file can be accessed by:

sudo nano /etc/default/locale

Add the missing values:

LANG=”en_US.UTF-8”
LC_ALL=en_US.UTF-8
LANGUAGE=en_US:en

• Now install Linux, Apache, and PHP:

sudo apt-get install lamp-server^

Then install PHP dependencies for Prestashop:

sudo apt-get install php5-curl php5-gd php5-mcrypt php5-memcache php5-intl

Then enable module php5-mcrypt with:

sudo php5enmod mcrypt

Then we need to edit the PHP configuration file. To access the config file run

sudo nano /etc/php5/apache2/php.ini

There find and add or modify the following lines:

memory_limit = 1024M
upload_max_filesize = 16M
max_execution_time = 60
file_uploads = On
allow_url_fopen = On
register_globals = Off

In the end just run

sudo service apache2 restart

Then we need to change the order in which Apache serves files. To modify the file run :

sudo /etc/apache2/mods-enabled/dir.conf

Make sure index.php is first:

<IfModule mod_dir.c>

  DirectoryIndex index.php index.html index.cgi index.pl index.xhtml index.htm

Step 3: Set up an external DB

AWS RDS offers a variety of options choose Postgres

AWS RDS Engine options

After the selection create a database by dining the following

1. The VPC should be the same as the EC2 instance
2. Add a security group with the right ports and sources. 
3. Remember DB name, username, password, endpoint, and port.

Cool! Your external DB should be up and running now. And the PrestaShop PostgreSQL connection is done.

Conclusion

With the popularity of eCommerce rising, the solutions that host, create, and do eCommerce have become very important. Prestashop is a free-to-use solution that is used by hundreds of thousands of users worldwide, to create eCommerce websites and host them. Ecommerce does require storage and Postgres is a leading RDBMS solution that is stable and reliable under heavy loads. By connecting Prestashop PostgreSQL together, users can leverage better storage in the Postgres database as well as create good websites. This article provided two methods that can be used to connect PrestaShop PostgreSQL together.

Companies use various trusted sources, which provide many benefits, but transferring data from them into a data warehouse is a hectic task. The Automated data pipeline helps solve this issue, and this is where Hevo comes into the picture. Hevo Data is a No-code Data Pipeline and has an awesome 150+ pre-built Integrations that you can choose from. Sign up for Hevo’s 14-day free trial and experience seamless data migration.