By using tablespaces, an administrator can control the disk layout of a PostgreSQL installation. This is useful in at least two ways. First, if the partition or volume on which the cluster was initialized runs out of space and cannot be extended, a tablespace can be created on a different partition and used until the system can be reconfigured.

Second, tablespaces allow an administrator to use knowledge of the usage pattern of database objects to optimize performance. For example, an index which is very heavily used can be placed on a very fast, highly available disk, such as an expensive solid state device. At the same time a table storing archived data which is rarely used or not performance critical could be stored on a less expensive, slower disk system.

To define a tablespace, use t

The post Manage Tablespaces appeared first on Hivelocity Hosting.

The post What database software does Hivelocity support? appeared first on Hivelocity Hosting.

The post Why should I disable autocommit while dumping a database? appeared first on Hivelocity Hosting.

If this happens, you will see a kernel message
Out of Memory: Killed process 12345 (postmaster).

This indicates that the postmaster process has been terminated due to memory pressure. Although existing database connections will continue to function normally, no new connections will be accepted. To recover, PostgreSQL will need to be restarted.

One way to avoid this problem is to run PostgreSQL on a machine where you can be sure that other processes will not run the machine out of memory.

On Linux 2.6 and later, a better solution is to modify the kernel’s behavior so that it will not “overcommit” memory. This is done by selecting strict overcommit mode via sysctl:

sysctl -w vm.overcommit_memory=2

or placing an equivalent entry in /etc/sysctl.conf. You may also wish to modify the related setting vm.overcommit_ratio. For details see the kernel documentation file Documentation/vm/overcommit-accounting.

The post How does memory overcommit affect PostgreSQL? appeared first on Hivelocity Hosting.

To add a Unix user account to your system, look for a command useradd or adduser. The user name postgres is often used, and is assumed throughout this book, but you can use another name if you like.

The post Create PostgreSQL user account appeared first on Hivelocity Hosting.

./configure
gmake
su
gmake install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test

The post Install PostgreSQL from source appeared first on Hivelocity Hosting.

The Service Manager area enables you to start and stop available services on your server. The following services are available:

• antirelayd – The Daemon controlling POP before SMTP
• clamd – ClamAV Virus Scanner
• entropychat – cPanel’s HTML-based Chat Server
• exim – SMTP Server
• eximstats – Daemon that monitors SMTP Bandwidth usage.
• httpd – Web Server (Apache)
• interchange – Shopping Cart (E-Commerce) Server
• melange – Java-based Chat Server
• mysql – mySQL Database Server
• named – name server (BIND)
• pop – POP3 Mail Server
• postgresql – PostgreSQL Database Server
• spamd – SpamAssassin server

NOTE: If you choose to disable this, you need to disable the SpamAssassin option from all customers as well. Refer to Tweaking Settings for more information on disabling this setting.

• syslogd – Daemon for logging system issues / activity.
• tomcat – Tomcat JSP Server (if installed).
• exim on another port – Run another copy of exim on a different port number, as some providers block 25, the standard port number.

The post Service Manager appeared first on Hivelocity Hosting.

The post Restart Services appeared first on Hivelocity Hosting.

The post Take postgresql database backup appeared first on Hivelocity Hosting.

Postgresql

Using following command you can take backup of postgresql database.

# pg_dump -u -C -f backupfile.sql database_name

You can restore pgsql database using following command

# pg_restore -v -C -O -d database_name backupfile.sql.tar

Mysql

Using following command you can take backup of mysql database.

# mysqldump database_name > backup.sql

You can restore mysql database using following command

# mysql database_name

The post Restore and take Backups of Database from shell appeared first on Hivelocity Hosting.

===========================================
Step 1
===========================================
Upgrade to cPanel 7.4.0 build 45 or later
===========================================

===========================================
Step 1a (If you do not have 7.2.x or earlier installed skip this step)
===========================================
Login to a root shell via ssh or the console.

If you have Postgres 7.2.x installed, backup your databases using pg_dumpall or some other method.

Move the postgres data directory somewhere else. ie

mv /var/lib/pgsql /var/lib/pgsql.old
===========================================

===========================================
Step 2
===========================================
Login to a root shell via ssh or the console and run

/scripts/installpostgres
===========================================

===========================================
Step 2a (skip this step if you are not upgrading from postgres 7.2 or earlier
===========================================
Restore your sql dump created with pg_dumpall or by some other method using psql or some other method
===========================================
29110310

===========================================
Step 3
===========================================
Login to Web Host Manager and choose “Postgres Config” under Server Setup.

Set a postgres password to anything of your choosing. You should avoid using any non-alpha numeric charaters as these cause problems.
===========================================

===========================================
Step 4
===========================================
Use the “Postgres Config” option in WHM to install a postgres pg_hba.conf file. You can skip this step if you would like to setup your pg_hba.conf file manually. cPanel should work fine with md5 passwords or plaintext passwords
===========================================

The post Installing Postgresql appeared first on Hivelocity Hosting.