Table of contents
- 1. How to start/stop the PostgreSQL server?
- 2. What is the default configuration?
- 2.1.1. PostgreSQL socket
- 3. How to connect to the PostgreSQL database?
- 4. How to change the PostgreSQL password?
- 5. How to create a database backup?
- 6. How to restore a database backup?
- 7. How to install PostGIS in the PostgreSQL database on Windows?
- 8. How to install PostGIS in the PostgreSQL database on Linux?
- 9. How to debug errors?
How to start/stop the PostgreSQL server?
The Stacks include a graphical tool to manage the servers easily. You can find the "manager-windows.exe", "manager-osx" or "manager-linux" tool in your installation directory. Using this tool, you can Start, Stop or Restart the servers and check the log files. You can click on the icon to start it.
On Windows: You can also start the Manager tool from shortcuts: Start -> Program Files -> BitNami Stack -> Manager tool
If you prefer, you can use the "ctlscript.sh" utility from the command line. This script is in the installation directory.
On OS X: You can start the Manager tool from the installation directory or you can use the "ctlscript.sh" utility from a Terminal.
host:~ user$ cd /Applications/application-version host:~ user$ ./ctlscript.sh start
On Linux:
$ cd ~/applicaton-version $ ./ctlscript.sh start
What is the default configuration?
PostgreSQL's grant tables are created during the BitNami Stack installation process. The grant tables define the initial PostgreSQL user accounts and their access privileges. The default configuration consists of:
- A privileged account with a username of 'postgres'. The initial password for this account is the same you specified in the setup wizard. For Virtual Appliances or AMIs the default password is "bitnami".
It is strongly recommended that you do not have empty passwords for any user accounts before using the server for any production work.
For security reasons, the PostgreSQL database does not let external connections by default. You only can connect from the local machine.
PostgreSQL socket
On Unix the PostgreSQL clients can connect to the server using an Unix socket file. In a BitNami Stack the default socket file is created in /installdir/postgres/.s.PGSQL.5432. Usually when you use a postgresql client tool included in the stack you will not need to specify the socket for the connection.
How to connect to the PostgreSQL database?
You can connect to PostgreSQL database from the same computer where it is installed.
- Open the BitNami console
- Run the postgresql client:
$ psql -U postgres
You can also run the postgreSQL client directly "/installdir/postgresql/bin/psql". If you have installed phpPgAdmin component, you can manage the PostgreSQL database from a browser. You can find more info at phpPgAdmin.
How to change the PostgreSQL password?
These are the default credentials for the PostgreSQL database.
- username: postgres
- password: bitnami (for BitNami AMIs), <application password> (for BitNami Cloud Hosting and Native Installers)
You can change the "postgres" user password from the command line:
$ psql -U postgres postgres=# alter user postgres with password 'YOUR_NEW_PASSWORD'; postgresl=# \q
How to create a database backup?
If you are interested in a backup of only the database data, you can create a dump file using the “pgdump” tool.
$ /installdir/postgresql/bin/pg_dump -U postgres database_name > backup.sql
This operation could take some time depending on the data size.
How to restore a database backup?
Once you have the backup file, you can restore it.
$ pgsql -U postgres -p 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, you can find the value for BITNAMI_USER_PASSWORD below in the application configuration file. This depends on the application and you can find this information in the application page itself.
$ psql -U postgres drop database database_name; create database database_name; create user database_user; alter role database_user with password 'BITNAMI_USER_PASSWORD'; grant all privileges on database database_name to database_user; alter database database_name owner to database_user; $ psql -U postgres database_name < backup.sql
Note that the steps previously described will only back up the state of your database. Other files that you should take into account when performing a backup are files that have been uploaded to the application. These files are stored in the application folder itslef so you can copy this folder to have a backup of your uploaded files.
How to install PostGIS in the PostgreSQL database on Windows?
Since BitNami WAPP Stack version 5.4.0-1 version, PostGIS is already included in the stack.
PostGIS adds support for geographic objects to the PostgreSQL object-relational database.These are the basic steps to install this extension in a BitNami WAPP Stack.
- Download and install the BitNami WAPPStack on your computer.
- Download PostGIS for Windows: http://postgis.refractions.net/download/windows/
- PostGIS 1.5.3 release for PostgreSQL 9.1 download link: http://postgis.refractions.net/download/windows/pg91/postgis-pg91-setup-1.5.3-2.exe
Note: The version of PostGIS must be compatible with the version of PostgreSQL installed with WAPPStack. At the moment of writing this artivle - Specify the PostgreSQL installation directory, "C:\Program Files\BitNami WAPPStack\postgresql" by default.
- When prompted the password, enter the same that you specified during the BitNami WAPPStack installation
- Accept defaults enable enable the "shp2pgsql" loader in PgAdmin III.
- Verify the "postgis" database has been successfully created. You can check it using the phpPgAdmin application at http://127.0.0.1/phppgadmin. The default user is "postgres" and the password is the same that you set during the WAPPStack installation.
How to install PostGIS in the PostgreSQL database on Linux?
Since BitNami LAPP Stack version 5.4.0-1 version, PostGIS is already included in the stack so it is not necessary to install it manually.
PostGIS adds support for geographic objects to the PostgreSQL object-relational database.These are the basic steps to install this extension in a BitNami LAPP Stack Ubuntu Virtual Appliance or AMI. Notice that the versions could be different.
$ sudo apt-get update $ sudo apt-get install build-essential libtool autoconf unzip wget
Download and install the dependencies:
$ wget http://download.osgeo.org/geos/geos-3.3.2.tar.bz2 $ bunzip2 geos-3.3.2.tar.bz2 $ tar -xvf geos-3.3.2.tar $ cd geos-3.3.2 $ ./configure $ make $ sudo make install This could take some time $ wget http://download.osgeo.org/proj/proj-4.8.0.tar.gz $ tar -xzvf proj-4.8.0.tar.gz $ cd proj-4.8.0 $ ./configure $ make $ sudo make install
Now download and install the PostGIS.
$ wget http://postgis.refractions.net/download/postgis-1.5.3.tar.gz $ tar -xzvf postgis-1.5.3.tar.gz $ cd postgis-1.5.3 $ ./configure $ make $ sudo make install
You can find more info about how to configure the PostgreSQL database using PostGIS at http://postgis.refractions.net/docs/ch02.html
How to debug errors?
The main PostgreSQL log file is created at /installdir/postgresql/postgresql.log file.
Comments