«

»

Mar 07

PostgreSQL Series, Part 2: Learning the Basics

Continuing with our series of utilizing PostgreSQL for your Geodatabase storage needs. Part 1 covered the installation and configuration of creating an empty geodatabase. Part 2 is focused on learning some of the basics: data access, how to backup/restore, and general database maintenance.

Geographic Database Consulting highly recommends installing the version of PostgreSQL from EnterpriseDB.com. They have a great community of resources and tutorials, as well as commercial support should you need it.

Being a long-time Oracle DBA, I am a professed command-line junkie. So most of these tips will revolve around doing things via command-line rather than the pgAdmin GUI. While the GUI is extremely useful, I learn better when doing things manually. So that is how I will teach it…

Accessing your data

Command Line

In Oracle, I use “sqlplus” religiously. The PostgreSQL equivalent is “psql”. To ensure your command line environment is set up correctly, you should source the “pg_env.sh” shell script, such as:

source /opt/PostgreSQL/9.1/pg_env.sh

This sets some environment variables such as your PATH, MANPATH, PGDATA directory, etc.

Once your environment is ready, you can access your database using the basic “psql” command:

 

[jeff@jeff-linux ~]$ psql -d sde -U sde

Password for user sde:

psql.bin (9.1.6)

Type “help” for help.

sde=#

 

In this example, the “-d” option specifies the database to which we want to connect (the SDE database). The “-U” option specifies our username, again SDE. Running a “psql -?” will show you all the command line options available.

Once you’re connected to your database, PSQL has some very cool command line shortcuts:

\d – List all tables, views, sequences, etc

\dt – List all tables only

\dv – List all views only

\d [TABLE] – Describe table

\dp [PATTERN] – List privileges

\di – List indexes

\? – Give you a list of ALL the command line shortcuts and options.

\q – Quit your command line session

 

Of course, from here you have all the basics of SQL commands:

sde=# select count(*) from parcels;

-[ RECORD 1 ]

count | 96422

 

If you have your features stored using PG_GEOMETRY, you can easily access your feature coordinates:

sde=# select objectid, AsText(shape) from sewerfitting where objectid < 10 order by 1;

objectid | astext

———-+——————————————

1 | POINT(1554694.43960908 452484.361655176)

2 | POINT(1539518.99997894 453961.00012055)

3 | POINT(1539889.00010729 453950.999992535)

4 | POINT(1538106.00004163 453966.00005655)

5 | POINT(1527366.99996763 471819.999945179)

6 | POINT(1534405.51543018 463114.31212908)

7 | POINT(1535457.74813516 451090.000053883)

8 | POINT(1556346.00005862 452487.999927178)

9 | POINT(1555743.99993007 452401.000119105)

(9 rows)

 

Overall, I really like the PSQL command environment. It provides everything I use in SQLPLUS on a daily basis, and once you learn the shortcuts, they are fantastic. Wish SQL PLUS had them…

PGADMIN GUI

For those that want a GUI, the pgAdmin tools are very full-featured. You can download them from the following URL:

http://www.pgadmin.org/

 

Backup and Restore

This is the single most important operation on any database. Beyond using these tips, you should dig deeper into the PostgreSQL documentation to make sure you are adequately protecting your database investment.

PostgreSQL offers a couple different types of backups, depending on your needs.

PG_DUMP

The “pg_dump” command is the simplest form of backup for PostgreSQL.

pg_dump -U sde sde -f /data/backups/pg_sde_2013_03_07.sql

This creates an ascii text file of the SDE database with all the CREATE TABLE and INSERT statements to fully recreate the database.

PG_DUMPALL

PostgreSQL provides a command to dump ALL the databases in your installation.

pg_dumpall > /data/backups/pg/pg_dumpall_2013_03_07.sql

You can confirm which databases are included in the backup with a simple grep command:

#grep “^[\]connect” /data/backups/pg/pg_dumpall_2013_03_07.sql

Returns:

\connect blank_sde

\connect postgres

\connect sde

\connect template1

\connect template_postgis

Restoring a database

Restoring a database is as simple as replaying the SQL file generated by the PG_DUMP or PG_DUMPALL:

psql –U sde –f /data/backups/pg_sde_2013_03_07.sql sde

Automating Backups

PostgreSQL provides a script which can be used to automate backups on Linux. The script is provided at the following URL:

http://wiki.postgresql.org/wiki/Automated_Backup_on_Linux

 

Data Maintenance

PostgreSQL databases require periodic maintenance known as vacuuming. Vacuuming accomplishes several tasks:

  • Recovers disk space occupied by updated or deleted rows.
  • Updates data statistics used by the query planner.
  • Protects against loss of very old data due to transaction ID wraparound.

Automated Vacuum Daemon

By default, in your POSTGRESQL.CONF file, there is a parameter called AUTOVACUUM=ON. This will take care of the basics, and should be left at the default settings unless you find a specific reason to change.

Manual Vacuuming

If you have some feature classes that are heavily updated, you can choose to manually vacuum those tables. For example, you could choose to manually vacuum your SDE_STATE_LINEAGES table every couple hours.

psql -U sde -d sde -c “vacuum verbose analyze sde_state_lineages”

Or you can vacuum the whole database with:

Psql –U sde –d sde –c “vacuum verbose analyze”

NOTE: you should avoid using “vacuum full” on your SDE_STATE_LINEAGES table (or any table during the day). Using “vacuum full” requires the table to be locked.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>