Table of Contents
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.
Connecting to your PostgreSQL database
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:
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 PostgreSQL database with phpPgAdmin
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.
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 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”)
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.
Managing your PostgreSQL database from the command line
Access your instance via the SSH console to gain access to
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
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.
Import a database
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
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