Perhaps the most popular and widely-used client application is pgAdmin. This app is free to download & use and there are versions available for almost every operating system, including the major ones like Windows, macOS, and Linux.

This Knowledgebase article will cover how to remotely connect to the database server using pgAdmin so you can run queries and more from your computer.

How to connect remotely to PostgreSQL Database using pgAdmin

First, you’ll need to download the latest version of pgAdmin. Click here to open the download pages for the application.

Once the application has been downloaded, install it on your computer.

The first time you open pgAdmin, you need to set up the server so the app knows where to connect. Click Add New Server.

The create server screen will open.

Proceed by giving the connection a name that you will remember, for example – it might say something like, my blog database, or ChemiCloud PostgreSQL.

When you’ve finished, click the Connection tab at the top.

Use the fields in the Connection tab to configure a connection:

• Enter the IP address or server hostname you wish to connect to. For example, this could be the IP address 1.2.3.4 or a server hostname, like server.serverhostgroup.com.
• Enter the listener port number of the server host in the Port field. The default is 5432.
• Use the Maintenance database field to specify the name of the database to which you want to connect.
• Use the Username field to specify the username assigned to the database to which you’re connecting.
• Use the Password field to provide a password that goes with the database user account you’re using.
• Check the box next to Save password? to instruct pgAdmin to save the password for future use if you want.

When you’ve finished, click Save. The pgAdmin app will attempt to connect to the server automatically, and the setting will be changed so the next time you open pgAdmin, you’ll be able to connect easily.

Are you struggling with PostgreSQL connectivity issues? ChemiCloud is the hosting solution designed to save you time! Check out our web hosting plans!

This knowledgebase article covers the various ways to import and export a PostgreSQL database.

How to export a PostgreSQL database

Method #1 Use the pg_dump application

The pg_dump command will allow you to export a PostgreSQL database to a file. To export using this method, follow the steps below:

Step 1: Connect to your web hosting account via SSH. If you are not familiar with how to do this, click here to review our KB article on the topic.

Step 2: Enter the following command using your keyboard and at the end, press enter/return on your keyboard. Be sure to replace the ‘username’ with your cPanel default account username and replace ‘dbname’ with the name of the database you wish to export.

pg_dump -U username dbname > dbexport.pgsql

You will be prompted for your default cPanel user account password in order to complete the export.

pg_dump: SQL command failed 

pg_dump: Error message from server: ERROR: permission denied for schema topology 

pg_dump: The command was: LOCK TABLE topology.topology IN ACCESS SHARE MODE

pg_dump -U username dbname -N topology -T spatial_ref_sys > dbexport.pgsql

Method #2 Use phpPGAdmin

Step 1: Login to your cPanel. There’s a lot of ways to do this, but the sure fire easiest way is to login to your Client Area, then open your cPanel.

Step 2: Scroll down to the Databases section and open phpPgAdmin.

Step 3: When phpPgAdmin opens, expand the Servers area from the left hand side, then expand PostgreSQL, and then click the name of the database you wish to export.

Step 4: From the menu at the top, click the Export button.

Under the Format column, select Structure and data. Under the Options column, in the Format list box, select SQL.

Then beneath Options, select Download.

Finally, click Export.

The file will be saved to your computer.

Tired of experiencing issues with your site? Get the best and fastest hosting support with ChemiCloud!  Check out our web hosting plans!

How to import a PostgreSQL database

Before you can import a PostgreSQL database, you must create a new database in cPanel and assign a user to it.

Method # 1: Use the psql application

Step 1: Using SCP, SFTP, FTP, or the cPanel File Manager > Upload File tool, upload the database you want to import to your hosting account.

Step 2: Connect to your web hosting account via SSH. If you are not familiar with how to do this, click here to review our KB article on the topic.

Step 3: Enter the following command, then press enter/return on your keyboard. Replace ‘username’ with your cPanel username and replace ‘dbname’ with the name of the database you wish to import:

psql -U username dbname < dbexport.pgsql

The ‘dbname’ database should now contain all the data that was in the database file you uploaded.

Method # 2: Use phpPgAdmin

To import a PostgreSQL database using phpPgAdmin, follow the steps below:

Step 1: Login to your cPanel. There’s a lot of ways to do this, but the sure fire easiest way is to login to your Client Area, then open your cPanel.

Step 2: Scroll down to the Databases section and open phpPgAdmin.

Step 3: From the left side of phpPgAdmin, expand the Servers area, then expand PostgreSQL, then click the name of the database you want to import data into.

Next click SQL from the row of options at the top of the page.

A white box will appear. You are able to enter text into this box, but you won’t need to. Instead, click the “Choose File” button beneath the text box.

When the file picker opens, choose the .postgresql file you wish to upload.

Then click Execute to instruct phpPgAdmin to import the data in that file into the database.

Additional Information

The official documentation for PostgreSQL can be very handy sometimes.

Find official PostgreSQL documentation here.

At ChemiCloud, we have just introduced the ability to use PostgreSQL to all of our customers. This Knowledge Base article will cover the differences between PostgreSQL and MySQL so you can make informed decisions when creating databases.

What is a PostgreSQL Database?

PostgreSQL is an enterprise-class object-relational database management system or ORDBMS. It was developed in the Computer Science Department at the University of California and pioneered many database concepts.

PostgreSQL is easy to set up and install and offers support for SQL and NoSQL type databases. It also has a great community supporting the application, which is happy to help users if they face issues when using PostgreSQL.

As a powerful, open-source object-relational database management system, it provides high performance with low maintenance effort due to its stability. PostgreSQL was the first ORDBMS to implement multi-version concurrency control or MVCC.

Some highlights of PostgreSQL are:

• It’s support for the vast amount of languages on Earth.
• It proxies advanced security features.
• It features geo-tagging support.

What are the differences between MySQL and PostgreSQL?

How to create A PostgreSQL Database in cPanel using the PostgreSQL Database Wizard

Step 1: Log in to your cPanel. There’s a lot of ways to do this, but the sure-fire easiest way is to log in to your Client Area, then open your cPanel.

Step 2: Look under the Databases section for the PostgreSQL Database Wizard. Click it to open the Wizard.

Step 3: You will be given the option to give your database a name:

After giving your database a name, click the blue Create Database button.

Step 4: Next you will have the option to create a username and password for the new database.

Be sure to use a secure password. If you need to generate one, use the Password Generator.

When you’ve entered the username + password, click the blue Create User button.

Step 5: Next, you will need to add the user to the database so that it has permission to access the database.

Click the Submit button to do this.

That’s it! Congrats, you’ve created a PostgreSQL database and a user + given that user permission to access the database.

Now you will have these options:

If you need to add another database, click the Add another database option.

If you need to add additional users to the database you created, click the Add another user for your PostgreSQL database.

If you want to return to the databases overview, click Return to PostgreSQL Databases.

To go back to the main cPanel screen, click Return Home.

How to connect to a PostgreSQL using PGAdmin in cPanel

Administering your PostgreSQL Databases using a GUI is very easy. At ChemiCloud, we include the phpPgAdmin tool, which is very similar to PHPMyAdmin.

Use the steps below to access phpPgAdmin.

Step 1: Log in to your cPanel. There are many ways to do this, but the sure-fire easiest way is to log in to your Client Area, then open your cPanel.

Step 2: Look in the Databases section for the phpPgAdmin tool and click it to open the tool.

Step 3: After clicking the phpPgAdmin tool, a new tab in your browser will open and you will see a list of your databases on the left side. Click on the database to open it in phpPgAdmin.

Step 4: With the database now open, you can use the tools in phpPgAdmin to administer and edit your database:

And that’s how you access the phpPgAdmin tool to administer your databases.