PostgreSQL on Simple Hosting

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.2 and 9.4 (PHP 5.6 only) are currently available on Simple Hosting and can be used with all languages.

You can create as many databases and users you want in your instance. You are only limited by the disk size, which you can increase at any time.

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 Simple Hosting.

The PostgreSQL database service is available on localhost at the default port 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

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.

You can access your Simple Hosting database by the phpPgAdmin URL that is available down in the 'Access' section of your instances' management page:

When you click on the link to log in, you will first need to enter your Simple Hosting user number and password (the one that you specified). Then you will see the phpPgAdmin login page:

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 control panel. Here you can create and manage your PostgreSQL databases.

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.

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.

After click 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.

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.

Access your instance via the SSH 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_instance:/srv/data$ psql
psql (9.4.7)
Type "help" for help.

postgres=#

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_instance:/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.

To import a database from your computer, you'll first need to use sFTP to place the .sql file on your instance. 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_instance:/srv/data$ psql < /srv/data/tmp/dump-database-YYYY-MM-DD.sql

• Official PostgreSQL documentation