Content

Bitnami PostgreSQL for 1&1 Cloud Platform

Description

PostgreSQL (Postgres) is an open source object-relational database known for reliability and data integrity. ACID-compliant, it supports foreign keys, joins, views, triggers and stored procedures.

First steps with the Bitnami PostgreSQL Stack

Welcome to your new Bitnami application running on 1&1! Here are a few questions (and answers!) you might need when first starting with your application.

What credentials do I need?

You need two sets of credentials:

The application credentials, consisting of a username and password. These credentials allow you to log in to your new Bitnami application.

The server credentials, consisting of an SSH username and password. These credentials allow you to log in to your 1&1 Cloud Platform server using an SSH client and execute commands on the server using the command line.

What is the administrator username set for me to log in to the application for the first time?

How do I get my SSH key or password?

What are the default ports?

A port is an endpoint of communication in an operating system that identifies a specific process or a type of service. Bitnami stacks include several services or servers that require a port.

Remember that if you need to open some ports you can follow the instructions given in the FAQ to learn how to open the server ports for remote access.

Port 22 is the default port for SSH connections.

The PostgreSQL access port is 5432. This port is closed by default, you must open it to enable remote access.

How to start or stop the services?

Each Bitnami stack includes a control script that lets you easily stop, start and restart services. The script is located at /opt/bitnami/ctlscript.sh. Call it without any service name arguments to start all services:

$ sudo /opt/bitnami/ctlscript.sh start

Or use it to restart a single service, such as Apache only, by passing the service name as argument:

$ sudo /opt/bitnami/ctlscript.sh restart apache

Use this script to stop all services:

$ sudo /opt/bitnami/ctlscript.sh stop

Restart the services by running the script without any arguments:

$ sudo /opt/bitnami/ctlscript.sh restart

Obtain a list of available services and operations by running the script without any arguments:

If you are using the 1&1 Control Panel, obtain your SSH credentials by following these steps:

Log in to the 1&1 Control Panel.

Navigate to the "Infrastructure -> Servers" section.

Look through the list of servers until you find the server you wish to modify. Click the server name.

In the "Features -> Server access" section, note the SSH username and click the "Show Password" link to obtain the corresponding SSH password.

Although you can use any SFTP/SCP client to transfer files to your server, this guide documents FileZilla (Windows, Linux and Mac OS X), WinSCP (Windows) and Cyberduck (Mac OS X).

Using a Password

Once you have your server's SSH credentials, choose your preferred application and follow the steps below to connect to the server using SFTP.

FileZilla

Follow these steps:

Download and install FileZilla.

Launch FileZilla and use the "File -> Site Manager -> New Site" command to bring up the FileZilla Site Manager, where you can set up a connection to your server.

Enter your server host name.

Select "SFTP" as the protocol and "Ask for password" as the logon type. Specify root as the user name and enter the server password.

Use the "Connect" button to connect to the server and begin an SFTP session. You might need to accept the server key, by clicking "Yes" or "OK" to proceed.

You should now be logged into the /root directory on the server. You can now transfer files by dragging and dropping them from the local server window to the remote server window.

If you have problems accessing your server, get extra information by use the "Edit -> Settings -> Debug" menu to activate FileZilla's debug log.

WinSCP

Follow these steps:

Download and install WinSCP.

Launch WinSCP and in the "Session" panel, select "SFTP" as the file protocol.

Enter your server host name and specify root as the user name.

From the "Session" panel, use the "Login" button to connect to the server and begin an SCP session. Enter the password when prompted.

You should now be logged into the /root directory on the server. You can now transfer files by dragging and dropping them from the local server window to the remote server window.

Cyberduck

Follow these steps:

Select the "Open Connection" command and specify "SFTP" as the connection protocol.

In the connection details panel, enter the server IP address, the username root and the SSH password.

Use the "Connect" button to connect to the server and begin an SFTP session.

You should now be logged into the /root directory on the server. You can now transfer files by dragging and dropping them from the local server window to the remote server window.

What is the default configuration?

The default configuration consists of:

A database cluster or directory under which all data will be stored. The default data directory in Bitnami is located at /opt/bitnami/postgresql/data.

Every connection to the PostgreSQL database server is made using the name of some particular role. A database superuser bypasses all permission checks, except the right to log in. Bitnami configures a default superuser role with a name of postgres. The postgres role has remote access to the database.

PostgreSQL socket

On Unix, PostgreSQL clients can connect to the server using an Unix socket file. Usually when you use a PostgreSQL client tool included in the Stack, you will not need to specify the socket for the connection.

The socket file is created at /opt/bitnami/postgresql/.s.PGSQL.5432.

PostgreSQL port

The default port in which PostgreSQL listens is 5432.

PostgreSQL log file

The main PostgreSQL log file is created at /opt/bitnami/postgresql/postgresql.log file. You can change the default error reporting and logging configuration settings in the postgresql.conf file.

NOTE: On some platforms, you may need root account privileges to view these files.

How to find the database credentials?

How to connect to the PostgreSQL database?

You can connect to the PostgreSQL database from the same computer where it is installed with the psql client tool.

$ psql -U postgres
You will be prompted to enter the *postgres* user password. This is the same as the [application password](/1and1/faq#how-to-find-application-credentials).

How to connect to PostgreSQL from a different machine?

For security reasons, the PostgreSQL port in this solution cannot be accessed over a public IP address. To connect to PostgreSQL from a different machine, you must open port 5432 for remote access. Refer to the FAQ for more information on this.

IMPORTANT: By default, the database port for the nodes in this solution cannot be accessed over a public IP address. As a result, you will only be able to connect to your database nodes from machines that are running in the same network. For security reasons, we do not recommend making the database port accessible over a public IP address. If you must make it accessible over a public IP address, we recommend restricting access to a trusted list of source IP addresses using firewall rules. Refer to the FAQ for information on opening ports in the server firewall.

Once you have an active SSH tunnel or you opened the port for remote access, you can then connect to PostgreSQL using a command like the one below.

Remember to replace SOURCE-PORT with the source port number specified in the SSH tunnel configuration or 5432 if you opened the port for remote access.

$ psql -h 127.0.0.1 -U postgres -p SOURCE-PORT

You will be prompted to enter the postgres user password. This is the same as the application password.

IMPORTANT: By default, the database port for the nodes in this solution cannot be accessed over a public IP address. As a result, you will only be able to connect to your database nodes from machines that are running in the same network. For security reasons, we do not recommend making the database port accessible over a public IP address. If you must make it accessible over a public IP address, we recommend restricting access to a trusted list of source IP addresses using firewall rules. Refer to the FAQ for information on opening ports in the server firewall.

Connect to your cloud server using PuTTY or another SSH client (instructions).

At the server console, edit the file /opt/bitnami/postgresql/data/pg_hba.conf and add the following at the end, then save the file:

host all all all md5

Edit the file /opt/bitnami/postgresql/data/postgresql.conf and replace this line

listen_address='127.0.0.1'

with:

listen_addresses = '*'

Save the file.

Restart the PostgreSQL server:

sudo /opt/bitnami/ctlscript.sh restart postgresql

Your PostgreSQL server is now configured to accept remote connections, and you can connect to it using pgAdmin 4. Follow these steps:

Launch pgAdmin 4.

Go to the "Dashboard" tab. In the "Quick Link" section, click "Add New Server" to add a new connection.

Select the "Connection" tab in the "Create-Server" window.

Then, configure the connection as follows:

Enter your server's IP address in the "Hostname/Address" field.

Specify the "Port" as "5432".

Enter the name of the database in the "Database Maintenance" field.

Enter your username as postgres and password (use the same password you used when previously configuring the server to accept remote connections) for the database.

Click "Save" to apply the configuration.

Check that the connection between pgAdmin 4 and the PostgreSQL database server is active. Navigate to the "Dashboard" tab and find the state of the server in the "Server activity" section:

How to create a database for a custom application?

These are the basic steps to create a new database and user for your applications:

Create a new role by executing the createuser command. With the options below, the new role will not be a superuser and will not have privileges for creating new databases or new roles (this is usually the default for the createuser command).

createuser -U postgres USER_NAME -S -D -R -P

You will be prompted to enter first the password for the new role and to reenter it, and then to enter the postgres role password.

Create a new database with the new role as the owner:

createdb -U postgres DATABASE_NAME -O USER_NAME

How to change the PostgreSQL root password?

You can modify the PostgreSQL password using the following command at the shell prompt:

You should now be able to connect to PostgreSQL with the new password.

How to create a database backup?

To back up only the database, create a dump file using the pg_dump tool.

$ pg_dump -U postgres DATABASE_NAME > backup.sql

This operation could take some time depending on the database size.

NOTE: The steps previously described will only back up the data contained inside your databases. There may be other files that you should take into account when performing a full backup, such as files that may have been uploaded to your application. Refer to your application's documentation for more details.

How to restore a database backup?

Once you have the backup file, you can restore it with a command like the one below:

$ psql -U postgres DATABASE_NAME < backup.sql

If you want to restore the database and the database schema does not exist, it is necessary to first follow the steps described below. As a side note, the value for the BITNAMI_USER_PASSWORD placeholder is included in the application credentials or, if the credentials were defined by the user, it is the same as the application password.