====== 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:

    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.

===== 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:

{{ :en:simple:postgre-access.png?nolink |}}

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:

{{ :en:simple:postgre-home.png?nolink |}}

By clicking on "PostgreSQL"  under "Servers" you will be taken to your login page to the database:

{{ :en:simple:postgre-login.png?nolink |}}

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")
  * 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.
</note>

===== Managing your PostgreSQL database from the command line =====

[[en:simple:console | 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).

<code>
hosting-user@my_instance:/srv/data$ psql
psql (9.4.7)
Type "help" for help.

postgres=#
</code>

==== 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":

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

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

Once the dump is completed, you can [[en:simple:sftp | 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 [[en:simple:sftp | 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: 

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


===== See also =====

  * [[http://wiki.postgresql.org/wiki/Main_Page|Official PostgreSQL documentation]]