How to Use a PostgreSQL Database with Your Web Hosting¶

Connecting to Your Database
Managing Your Database with phpPgAdmin
Managing Your Database from the Command Line
- Export a Database
- Import a Database
See Also

PostgreSQL is a popular database system famous for its speed, robustness and variety of features. While it is originally a SQL database, it also offers support for JSON and various formats.

PostgreSQL versions 9.6, 11, and 12 are currently available to use on Web Hosting and can be used with all languages.

You can create as many databases and users you want in your web hosting. You are only limited by the disk size, which you can increase at any time up to 1 TB.

The PostgreSQL database service can be managed from the console or from a Web interface. This article describes how to access, create and manage PostgreSQL databases on Web Hosting.

Connecting to Your Database ¶

The PostgreSQL database service is available on localhost and the default PostgreSQL port is 5432. A default user (hosting-db) and database (postgres) exist so you can quickly test your connection and perform management tasks.

Default connection settings:

Host: localhost
Port: 5432
User: hosting-db
Password: <none>
Database: postgres

The URL version looks like this:

tcp://hosting-db@localhost/postgres

Note

You are encouraged to create new users with strong credentials and new databases for your websites or application. You’ll find instructions on how to perform these and other management tasks below.

Managing Your Database with phpPgAdmin ¶

You can manage your Web Hosting database with phpPgAdmin. You can access it from the Administration section of your web hosting’s management page.

When you click on the link to log in, you will first need to enter your web hosting id and password, which is also located in the Administration section under Login information. After submitting the login credentials, you will then see the phpPgAdmin login page.

Next, by clicking on PostgreSQL under Servers you will be taken to your login page to the database.

By default the phpPgAdmin user is hosting-db and there is no password. So just type in hosting-db as the user, leave the password empty and click GO.

This then brings you to the phpPgAdmin home, where you gain access to your database management interface. Here you can create and manage your PostgreSQL databases.

Create a Database ¶

Click on the Create database link to open the database creation screen.

Choose a name for your database and leave or adapt the default settings according to your needs.

Remember that you can create and delete as many databases as you want.

Create a User ¶

Click on the Roles tab button that you’ll find next to the Databases tab to open the users list.

By default, you’ll see that a user called hosting-db already exists. Click the Create role link to open the user creation screen.

Choose a username and a password, then select the appropriate permissions for that user. Click the Create button to create the user.

Export a Database ¶

After clicking on a database name from the databases list, click on the Export button that you can find on the right-hand side of the tab bar.

You can choose to export the data, the structure, or both the data and structure of the database. You can also select the format of the exports.

To fully backup a database, you can select the following options :

“Structure and data”

Format “SQL” on both select boxes (instead of “COPY”)

Download

Then click the Export button to start the download.

Import a Database ¶

Start by creating an empty database, then click on its name from the databases list.

Assuming you have a SQL file containing the database dump, you should click on the SQL button in the tab bar. Below the text area, click on the Choose file button to select your dump file.

Once you select the file, the upload will start and the database will be created.

Note

If the file is too big and the upload fails, you can still upload it via sFTP and import the database dump via the SSH console. Read the section below to learn more.

Managing Your Database from the Command Line ¶

First, access your web hosting via the Emergency console to gain access to psql.

Once connected, you won’t need to enter a username or a password to connect to the PostgreSQL database service via the command line until you have deleted the default hosting-db user (who has Unix-style access).

hosting-user@my_web_hosting:/srv/data$ psql
psql (9.4.7)
Type "help" for help.

postgres=#

Export a Database ¶

Export a database from the command with the pg_dump tool and a standard redirect > character to write the output to a file.

For example, to dump the postgres database into a file called dump-postgres-YYYY-MM-DD.sql:

hosting-user@my_web_hosting:/srv/data$ pg_dump postgres > ~/dump-postgres-YYYY-MM-DD.sql

If the file you specify does not exist, it’ll be created in the process.

Once the dump is completed, you can use sFTP to download the file onto your computer, for example.

Import a Database ¶

To import a database from your computer, you’ll first need to use sFTP to place the .sql file on your web hosting. You can place the file, for example, in the home directory or in /srv/data/tmp.

Assuming that your file is called dump-database-YYYY-MM-DD.sql, you could run this simple command to import your database:

hosting-user@my_web_hosting:/srv/data$ psql < /srv/data/tmp/dump-database-YYYY-MM-DD.sql