Bitnami PostgreSQL Installer

NOTE: Before running the commands shown on this page, you should load the Bitnami stack environment by executing the installdir/use_APPNAME script (Linux and Mac OS X) or by clicking the shortcut in the Start Menu under "Start -> Bitnami APPNAME Stack -> Application console" (Windows). Learn more.
NOTE: When running the commands shown on this page, replace the installdir placeholder with the full installation directory for your Bitnami stack.

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! Here are a few questions (and answers!) you might need when first starting with your application.

What are the system requirements?

Before you download and install your application, check that your system meets these requirements.

How do I install the Bitnami PostgreSQL Stack?

Windows, OS X and Linux installer
  • Download the executable file for the Bitnami PostgreSQL Stack from the Bitnami website.

  • Run the downloaded file:

    • On Linux, give the installer executable permissions and run the installation file in the console.
    • On other platforms, double-click the installer and follow the instructions shown.

Check the FAQ instructions on how to download and install a Bitnami Stack for more details.

The application will be installed to the following default directories:

Operating System Directory
Windows C:\Bitnami\APPNAME-VERSION
Mac OS X /Applications/APPNAME-VERSION
Linux /opt/APPNAME-VERSION (running as root user)
OS X VM
  • Download the OS X VM file for the Bitnami PostgreSQL Stack from the Bitnami website.
  • Begin the installation process by double-clicking the image file and dragging the WordPress OS X VM icon to the Applications folder.
  • Launch the VM by double-clicking the icon in the Applications folder.

What credentials do I need?

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

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

  • For Windows, Linux and OS X installers, the username was configured by you when you first installed the application.
  • For OS X VMs, the username can be obtained by clicking the Bitnami badge at the bottom right corner of the application welcome page.

What is the administrator password?

  • For Windows, Linux and OS X installers, the password was configured by you when you first installed the application.
  • For OS X VMs, the password can be obtained by clicking the Bitnami badge at the bottom right corner of the application welcome page.

How to start or stop the services?

Linux

Bitnami native installers include a graphical tool to manage services. This tool is named manager-linux-x64.run on Linux and is located in the installation directory. To use this tool, double-click the file and then use the graphical interface to start, stop or restart services. Server log messages can be checked in the "Server Events" tab.

Management tool

The native installer also includes a command-line script to start, stop and restart applications, named ctlscript.sh. This script can be found in the installation directory and accepts the options start, stop, restart, and status. To use it, log in to the server console and execute it following the examples below:

  • Call it without any service names to start all services:

      $ sudo installdir/ctlscript.sh start
    
  • Use it to restart a specific service only by passing the service name as argument - for example, mysql, postgresql or apache:

      $ sudo installdir/ctlscript.sh restart mysql
      $ sudo installdir/ctlscript.sh restart postgresql
      $ sudo installdir/ctlscript.sh restart apache
    
  • Obtain current status of all services:

      $ installdir/ctlscript.sh status
    

The list of available services varies depending on the required components for each application.

Mac OS X

Bitnami native installers include a graphical tool to manage services. This tool is named manager-osx on Mac OS X and is located in the installation directory. To use this tool, double-click the file and then use the graphical interface to start, stop or restart services. Server log messages can be checked in the "Server Events" tab.

Management tool

The native installer also includes a command-line script to start, stop and restart applications, named ctlscript.sh. This script can be found in the installation directory and accepts the options start, stop, restart, and status. To use it, log in to the server console and execute it following the examples below:

  • Call it without any service names to start all services:

    $ sudo installdir/ctlscript.sh start
    
  • Use it to restart a specific service only by passing the service name as argument - for example, mysql or apache:

     $ sudo installdir/ctlscript.sh restart mysql
     $ sudo installdir/ctlscript.sh restart apache
    
  • Obtain current status of all services:

     $ installdir/ctlscript.sh status
    

The list of available services varies depending on the required components for each application.

NOTE: If you are using the stack manager for Mac OS X-VM, please check the following blog post to learn how to manage services from its graphical tool.

Windows

Bitnami native installers include a graphical tool to manage services. This tool is named manager-windows.exe on Windows and is located in the installation directory. To use this tool, double-click the file and then use the graphical interface to start, stop or restart services. Server log messages can be checked in the "Server Events" tab.

Management tool

The Windows native installer creates shortcuts to start and stop services created in the Start Menu, under "Programs -> Bitnami APPNAME Stack -> Bitnami Service". Servers can also be managed from the Windows "Services" control panel. Services are named using the format APPNAMESERVICENAME, where APPNAME is a placeholder for the application name and SERVICENAME is a placeholder for the service name. For example, the native installer for the Bitnami WordPress Stack installs services named wordpressApache and wordpressMySQL.

These services will be automatically started during boot. To modify this behaviour, refer to the section on disabling services on Windows.

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 installdir/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.

Check our recommendations for a production server.

PostgreSQL version

To obtain the PostgreSQL version, execute the following command:

$ postgres --version

PostgreSQL configuration files

The PostgreSQL configuration settings file is located at installdir/postgresql/data/postgresql.conf.

The PostgreSQL client authentication configuration file is located at installdir/postgresql/data/pg_hba.conf.

The PostgreSQL official documentation has more details on how to configure the PostgreSQL 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 installdir/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 installdir/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 secure your server?

Once you have created a new database and user for your application, connect your applications to the PostgreSQL server using that database and password.

It is strongly recommended that you do not have empty passwords for any user accounts when using the server for any production work.

Allow remote access only from the database user associated with the database name by editing the pg_hba.conf file on the database server host as explained below.

  • Comment out the line that allow remote access to all users:

     #host all all all md5
    
  • Add a line for the new user:

     host DATABASE_NAME USER_NAME all md5
    
  • If your application will be connected from a fixed domain or IP address, you should update your pg_hba.conf file to only accept connections from that server instead:

     host DATABASE_NAME USER_NAME applicationserver.domain.com md5
    

    or

     host DATABASE_NAME USER_NAME IP_ADDRESS 255.255.255.255 md5
    
  • Change your postgres user password as explained in this section.

How to find the database credentials?

  • Database username: postgres.
  • Database password: The password entered during the installation process.

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](/installer/faq#how-to-find-application-credentials).

How to connect to PostgreSQL from a different machine?

IMPORTANT: Bitnami Native Installers do not modify the firewall configuration of your computer, therefore the PostgreSQL ports could be open which is a significant security risk. You are strongly advised to close the PostgreSQL ports (refer to the FAQ for more information on this).

Once you have an active SSH tunnel or if you did not close 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 did not close 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.

How to change the PostgreSQL root password?

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

$ psql -U postgres
postgres=# alter user postgres with password 'NEW_PASSWORD';
postgresl=# \q

How to reset the PostgreSQL password?

If you don't remember your PostgreSQL database password, you can follow the steps below to reset it to a new value:

  • Change the authentication method in the configuration file at installdir/postgresql/data/pg_hba.conf from md5 to trust and reload the configuration.

     $ sudo sed -ibak 's/^\([^#]*\)md5/\1trust/g' installdir/postgresql/data/pg_hba.conf
     $ sudo -u postgres pg_ctl reload
    
  • Connect to the PostgreSQL database and set the password to a new value:

     $ psql -U postgres
     postgres=# alter user postgres with password 'NEW_PASSWORD';
     postgresl=# \q
    
  • Finally, change the authentication method back to md5 and reload the old PostgreSQL configuration:

     $ sudo sed -i 's/^\([^#]*\)trust/\1md5/g' installdir/postgresql/data/pg_hba.conf
     $ sudo -u postgres pg_ctl reload
    

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

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 configure pgAdmin 4?

NOTE: This section assumes that you have downloaded and installed pgAdmin 4.

pgAdmin is the most popular and feature-rich platform for administration and development of PostgreSQL databases. Check the pgAdmin official page for more information.

To connect to your remote PostgreSQL database server using pgAdmin 4, follow these steps:

  • Open port 5432 in your firewall (on Linux only). For example, if you are using ufw, you would run the following commands:

    $ sudo ufw allow postgres/tcp
    

    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.

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

    host all all all md5
    
  • Edit the file installdir/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 installdir/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.

    pgAdmin 4 configuration

  • 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.

    pgAdmin 4 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:

    pgAdmin 4 configuration

How can I run a command in the Bitnami PostgreSQL Stack?

Load the Bitnami Console and then run the command as usual.

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.

$ psql -U postgres
drop database DATABASE_NAME;
create database DATABASE_NAME;
create user USER_NAME;
alter role USER_NAME with password 'BITNAMI_USER_PASSWORD';
grant all privileges on database DATABASE_NAME to USER_NAME;
alter database DATABASE_NAME owner to USER_NAME;
$ psql -U postgres DATABASE_NAME < backup.sql
nativeInstaller

Bitnami Documentation