| Wiki Markup |
|---|
During the time of this article, the [instructions on the Ubuntu wiki|https://help.ubuntu.com/community/PostgreSQL] were pretty light and from what I can see not quite complete. These instructions are currently written for PostgreSQL 8.x. |
...
h2. Installing |
...
PostgreSQL h3. Ensure UTF-8 Encoding is Set |
...
Make sure that your operating system has the right encoding set as explained in the "Select Locale" part of my [Ubuntu Linux Base Server|bonsai:1.0 Setup Ubuntu Linux Base Server] article. In the case of my Ubuntu server it was installed with the minimal configuration and was missing the utf-8 locale. |
...
Install
The Ubuntu install is simply,
| Code Block |
|---|
h3. Install The Ubuntu install is simply, {code language=bash}sudo apt-get install postgresql # installs postgresql{code} Similar to how Apache is installed, Ubuntu has it's own standard. Here are the keynotes. |
...
You will now have a *postgresql* user on your system. This account is reserved for running Postgresql in a secure manner. This account neither has a password or a home directory. I will show you how to use it as we go forward. |
...
The postgresql service is also added to the system to start automatically. |
...
Finally here are some directories to be aware of, |
...
* /var/lib/postgresql/8.4/main - location of the main database that can only be viewed and modified by the user *postgresql*. |
...
* /etc/postgresql/8.4/main - location of all configuration files. |
...
* /var/log/postgresql/ - location where Ubuntu keeps all the logs and automatically is set to archive and roll over. |
...
*That's all there is for the installation. Ubuntu apt-get makes it easy and does all the hard work for you. |
...
Further Details
...
* h3. Further Details You might also be interested to know that Ubuntu's apt-get installer does a number of other things you would have had to manually configure if you installed manually, |
...
* Initialize a database storage area on disk called the database cluster. |
...
* ... |
...
{warning | ||
| Code Block | ||
|---|---|---|
| ||
}In order to complete this section I need to figure out how to do a configured portable setup of PostgreSQL. |
Initial Setup
Change the Default postgres Database User Account
If you want to manage postgresql outside of the command line, you must change the default password in postgresql.
The first step, is to step into the postgresql shell launching the program psql as the user postgres.
{warning} h2. Initial Setup h3. Change the Default *postgres* Database User Account If you want to manage postgresql outside of the command line, you must change the default password in postgresql. The first step, is to step into the postgresql shell launching the program psql as the user postgres. {code:lang=bash}sudo -u postgres psql postgres # -u postgres makes the psql run as the postgres user{code} Here is what it looks like once you are in, |
...
{code | ||
| ||
:lang=bash}psql (8.4.1) Type "help" for help. postgres=#{code} All commands are prefixed by the the \. So to change the postgres account password, |
...
{code | ||
| ||
:lang=bash}postgres=# password postgres{code} Note that this changes the account inside of postgres, not the operating system account postgres. Finally exit the postgres shell, |
...
{code | ||
| ||
:lang=bash}postgres=# quit{code} With this change you can now use a client software such as pgAdmin III. Note that be by default, postgres is very secure. As such with this change you will be able to use pgAdmin III in only the following 2 scenarios, |
...
# Using the pgAdmin III installed on the same system as Postgresql. |
...
# From another machine using an SSH Tunnel. |
...
Read further for more details on connecting. |
...
h2. Connecting to the |
...
GUI Client
Besides the command line interface, I like to be able to manage the database using a remote GUI client.
...
Database h3. GUI Client Besides the command line interface, I like to be able to manage the database using a remote GUI client. h4. pgAdmin 3 from www.pgadmin.org |
...
[pgAdmin 3|http://www.pgadmin.org/index.php] is one of the most popular GUI clients. It is cross platform so I happen to run it on my Windows desktop. |
...
{tip |
}Note: for the nit picky users like me, in the Windows environment I personally hate installers, so I install this in a test machine, copy out the directory and then just use on my other systems without having to install and have strange things in my Windows registry. {tip} From my Windows desktop I SSH into the Ubuntu server and tunnel port of 5432 to connect. See, [Secure TCP/IP Connections with SSH Tunnels|http://developer.postgresql.org/pgdocs/postgres/ssh-tunnels.html] for more details. |
...
I find using SSH to be the most secure method. In some cases you may need to [allow remote administration |
...
Server Instrumentation Warning
Note, the very first time I connected with pgAdmin 3 I got a warning on Server instrumentation. I am currently trying to understand if these messages should be addressed or ignored. My gut is saying that I do not need this in a stripped down system. It's most likely nice to have tools for DB admins to get more data.
Here is the warning,
| Warning |
|---|
Server instrumentation The server lacks instrumentation functions. pgAdmin III uses some support functions that are not available by default in all PostgreSQL versions. These enable some tasks that make life easier when dealing with log files and configuration files. When compiling from source, the necessary files can be found in the xtra subdirectory of the pgAdmin source tree. For PostgreSQL 8.0, copy the admin directory under the postgresql contrib source directory, make and make install from there. For PostgreSQL 8.1, use the admin81 directory for that. PostgreSQL 8.2 and above include the instrumentation functions in the "adminpack" contrib module. After the module is installed, you need to create the instrumentation functions in your maintenance database using the admin.sql script (admin81.sql for PostgreSQL 8.1) which are usually located in the pgsql share directory (e.g.|bonsai:Allowing Remote Administration in PostgreSQL]. h2. Server Instrumentation Warning Note, the very first time I connected with pgAdmin 3 I got a warning on Server instrumentation. I am currently trying to understand if these messages should be addressed or ignored. My gut is saying that I do not need this in a stripped down system. It's most likely nice to have tools for DB admins to get more data. Here is the warning, {warning}Server instrumentation The server lacks instrumentation functions. pgAdmin III uses some support functions that are not available by default in all PostgreSQL versions. These enable some tasks that make life easier when dealing with log files and configuration files. When you install PostgreSQL 8.0 or up using the Windows installer, you just need to select the "admin" or "adminpack" module.c When compiling from source, the necessary files can be found in the xtra subdirectory of the pgAdmin source tree. For PostgreSQL 8.0, copy the admin directory under the postgresql contrib source directory, make and make install from there. For PostgreSQL 8.1, use the admin81 directory for that. PostgreSQL 8.2 and above include the instrumentation functions in the "adminpack" contrib module. After the module is installed, you need to create the instrumentation functions in your maintenance database using the admin.sql script (admin81.sql for PostgreSQL 8.1) which are usually located in the pgsql share directory (e.g. /usr/local/pgsql/share) Extended server instrumentation is not supported for PostgreSQL 7.3 and 7.4. |
Backup of a Database
| Warning |
|---|
This section needs to be written and tested. |
{warning} h2. Backup of a Database {warning}This section needs to be written and tested.{warning} [http://www.postgresql.org/docs/8.1/static/backup.html] \- not too clear, for example how do I specify a user? |
...
[http://www.thegeekstuff.com/2009/01/how-to-backup-and-restore-postgres-database-using-pg_dump-and-psql/] \- looks promising, syntax looks very different from official manual though. |
...
Also, there is a backup interface via the gui... figure out how to get the command line equivalent. It worked well enough for my 52MB database. Just took a while as it went over the network (gui ran from a client machine). |