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 version 9.6 is 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 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 Simple Hosting.
Connecting to your 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 database with phpPgAdmin¶
You can manage your Simple Hosting database with phpPgAdmin. You can access it from the Database section of your instance management page.
When you click on the link to log in, you will first need to enter your instance id and password, which you can find in the Control Panel section of your instance’s management page. Then you will 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”)
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.
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 instance via the Emergency 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
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