Private PostgreSQL instances run under your user account, with configuration and data files stored in an application directory, ~/webapps/instance , where instance is the name of the application.
On CentOS 7 servers, instances run PostgreSQL 9.4. On CentOS 6 servers ( web300 and greater), instances run PostgreSQL 9.1. On CentOS 5 servers, instances run PostgreSQL 8.3.
Every instance comes with twocron jobs. One attempts to start the database every ten minutes, if it is not already running. The other attempts to create a dump of your database once per day.
An instance’s log files can be found in the ~/webapps/instance/data/pg_log/ directory.
17.2.1 Installing a Private PostgreSQL Instance
To install a private PostgreSQL instance:
1. Log in to the control panel.
2. Click Domains / websites → Applications. The list of applications appears.
3. Click the Add new application button. The Create a new application form appears.
4. In the Name field, enter a name for the application.
5. In the App Category menu, click to select PostgreSQL.
6. In the App Type menu, click to select PostgreSQL private instance.
7. If applicable, in the Machine menu, click to select a web server.
8. Click the Save button.
The application is installed and added to the list of applications.
17.2.2 Starting, Restarting, and Stopping a Private PostgreSQL Instance
To manually start, restart, or stop a private PostgreSQL instance,open an SSH session to your account, then enter one of these management commands:
• Start: $HOME/webapps/instance/bin/start
• Restart: $HOME/webapps/instance/bin/stop && $HOME/webapps/instance/bin/start
• Stop: $HOME/webapps/instance/bin/stop
where instance is the name of the private database instance application as it appears on the control panel, and press Enter.
17.2.3 Creating Private PostgreSQL Databases and Users
To use your database, you will likely require databases and users. To create a database and user with privileges on that database:
1. Open an SSH session to your account.
2. Start a PostgreSQL interactive terminal. Enter psql -h localhost -p port postgres , where portis the instance’s port number and press Enter . A terminal prompt appears.
3. Create a database. Enter CREATE DATABASE database_name; , where database_name is the name of the database, and press Enter .
4. Create a user. Enter CREATE USER username WITH PASSWORD ’pass’; , where username is the new username and pass is the user’s password, and press Enter .
See also:
SeeStrengthening Passwordsfor important information about choosing passwords.
5. Grant the user privileges on the database. Enter
GRANT ALL PRIVILEGES ON DATABASE database_name to username; and press Enter . 6. Quit the interactive terminal. Enter \q and press Enter .
The user can nowconnect to the databaseand run queries.
See also:
See the officialPostgreSQL documentationfor more information about creating and managing users and databases.
17.2.4 Importing Data from a Shared PostgreSQL Database
To move your data from a shared PostgreSQL database to a private database instance:
1. Dump the contents of your existing shared database to a file.
2. Open an SSH session to your account.
3. Enter psql -h localhost -p port -U database_user database_name < dump_file , where:
• port is the instance’s port number,
• database_user is a valid database username,
• database_name is a database that the user has permissions to use, and
• dump_file is the path the dump containing the contents of your shared database, and press Enter .
The contents of the dump file are loaded into the database.
17.2.5 Importing and Exporting Data from a Private PostgreSQL Database
You can use command-line tools to export (or dump) the contents of a PostgreSQL database to a file, or to import the contents of such a file to a PostgreSQL database.
Note: To minimize load and preserve performance on your server, please use ionice when importing or exporting databases larger than 500MB.
To use ionice , prefix your database commands with ionice -c2 -n6 . For example, replace pg_dump with ionice -c2 -n6 pg_dump .
Exporting
To export the contents of a private PostgreSQL database:
1. Open an SSH session to your account.
2. Enter
pg_dump --host localhost --port port --username database_user --file dump_file database_name,
• port is the instance’s port number,
• database_user is a valid database username,
• database_name is a database that the user has permissions to read, and
• dump_file is the path to the dump file to be created, and press Enter .
Your dump file is created at the path specified.
Importing
To import a dump file into a private PostgreSQL database:
1. Open an SSH session to your account.
2. Enter
psql --host localhost --port port --username database_user database_name < dump_file, where:
• port is the instance’s port number,
• database_user is a valid database username,
• database_name is a database that the user has permissions to use, and
• dump_file is the path to the dump file, and press Enter .
Note: If your dump file was created as a PostgreSQL custom dump, then use pg_restore instead of psql to import the data.
Enter
pg_restore --host localhost --port port --username database_user --dbname database_name dump_file and press Enter .
The contents of the dump file are loaded into the database.
17.3 Configuring Applications for Private Database Instances
To connect an application to a private MySQL or PostgreSQL instance, use the following configuration details:
Host 127.0.0.1 (an IP address) or localhost (a host name) Port Use the port number assigned to your application.
Note: To find the port number:
1. Log in to the control panel
2. Click Domains / websites → Applications. The list of applications appears.
3. Click the name of the application.
The port number appears in the Port section.
Username A database user you’ve created previously.
Database A database you’ve created previously.
See also:
• Creating Private MySQL Databases and Users
• Creating Private PostgreSQL Databases and Users
The following sections provide examples of how to reconfigure applications for use with private database instances.
17.3.1 Configuring Django for a Private Database Instance
To reconfigure a Django application’s default project to use a private PostgreSQL instance:
1. If applicable,import data from a shared PostgreSQL database to a private database instance.
2. In a text editor, open ~/webapps/application/myproject/myproject/settings.py , where applicationis the name of the Django application as it appears in the control panel.
3. Find the DATABASES dictionary similar to the following:
DATABASES = { 'default': {
'ENGINE': 'django.db.backends.', 'NAME': '<DATABASE>',
'USER': '<USERNAME>', 'PASSWORD': '<PASS>', 'HOST': '<HOST>',
'PORT': '<PORT_NUMBER>', }
}
and modify the dictionary such that:
• django.db.backends. is replaced by django.db.backends.postgresql_psycopg2 ,
• <DATABASE> is the name of a database you’ve created previously,
• <USERNAME> is a database user you’ve created previously,
• <PASS> is the database user’s password,
• <HOST> is replaced by 127.0.0.1 , and
• <PORT_NUMBER> is the port number assigned to your private database instance.
See also:
To find the port number:
(a) Log in to the control panel
(b) Click Domains / websites → Applications. The list of applications appears.
(c) Click the name of the application.
The port number appears in the Port section.
4. Save and close the file.
5. Restart the Django application.
The Django application is configured to connect to the private PostgreSQL instance.
17.3.2 Configuring WordPress for a Private Database Instance
To reconfigure a WordPress application to use a private MySQL instance:
1. Import data from the application’s database to a private MySQL instance.
2. In a text editor, open ~/webapps/application/wp-config.php , where application is the name of the WordPress application.
3. Find a line starting with define(’DB_NAME’, and replace the line with
define(’DB_NAME’, ’database’);, where database is the name of a database you’ve created previously.
4. Find a line starting with define(’DB_USER’, and replace the line with
define(’DB_USER’, ’username’);, where username is a database user you’ve created previously.
5. Find a line starting with define(’DB_PASSWORD’, and replace the line with
define(’DB_PASSWORD’, ’password’);, where password is the database user’s password.
6. Find a line starting with define(’DB_HOST’, and replace the line with
define(’DB_HOST’, ’127.0.0.1:port’);, where port is the port number assigned to your private database instance.
7. Save and close the file.
EIGHTEEN
PYRAMID
Pyramidis an open source web application framework for Python.