...
With this change you can now use a GUI 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.
Connecting to the Database
GUI Client
Besides the command line interface, I like to be able to manage the database using a remote GUI client.
pgAdmin 3 from www.pgadmin.org
pgAdmin 3 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. |
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 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. /usr/local/pgsql/share) |
Backup of a Database
Warning |
---|
This section needs to be written and tested. |
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).
Further Reading
Page Tree | ||
---|---|---|
|