Backup and Restore is probably the most important thing to know how to do when you have a database with data you care about.
The utilities in PostgreSQL that accomplish these tasks are pg_restore, pg_dump, pg_dumpall, and for restore of plain text dumps - psql.
A lot of the switches used by pg_dump, pg_restore, pg_dumpall are common to all three and on rare cases, the switches used by each overlap but mean different things.
pg_dump and pg_restore are complementary. You use pg_dump to do hot backups of a database and pg_restore to restore it either to another database or to recover portions of a database.
Rather than trying to keep track of which switch works with which, we decided to combine all into a single cheat sheet with a column denoting which utility the switch is supported in.
Pretty much all the text is compiled from the --help switch of each.
Below is a Thumbnail view of the PostgreSQL 8.3 Dump Restore cheat sheet
that covers PostgreSQL 8.3 pg_dump, pg_dumpall, pg_restore utilities.
As many people who know us know we sit on several camps especially when it comes to databases.
The camps we sit on are growing rather than shrinking.
While we do have our favorites, we understand that peoples needs and comfort levels are different from ours and we try to take that into
consideration when making recommendations to people. The only thing that is generally true about the clientele we consult for is that they
fit one of the following features:
Very minimal bureaucratic structure - this generally rules out most fortune 500 companies
and shall we say smaller companies who are too bureaucratic for their own good
Dot com startups/Niche product developers who are looking to keep costs down to a minimum without too much fuss and are trying to produce a product to change the world
Small companies who have a relatively low IT budget, but are predominantly windows-based
Mid-sized companies predominantly windows-based or departments with decent IT staff,
who are looking for something their staff can easily maintain rather than simply keeping licensing costs down
It has come up as a topic of discussion, now that SQL Server 2008 is coming out soon and with its new fangled geodetic spatial support,
how does this change things?
Programming Design Patterns define recommended approaches of solving common application problems. Within design patterns is a subset of design patterns called Idioms.
Idioms you can think of as a strategy for expressing recurring constructs or if you will sub-problems and often take advantage of the special features of a language.
They tend to be specific to a programming language and can not be reused
in other languages they were not specifically designed for. To demonstrate the differences lets compare two design patterns we commonly use.
In our April Issue An Almost Idiot's Guide to PostgreSQL YUM
we covered using the new PostgreSQL Yum repository to install the PostgreSQL 8.3.1 release on Fedora, RedHat Enterprise, and CentOS. We also received numerous useful feedback from others on issues they
ran into and how they overcame them. The blog comments are definitely worth a read.
While there are numerous interesting use cases for the PostgreSQL inheritance structure, one of the key reasons people use it is for table partitioning strategies.
How do you make a stand-alone table a child of another table?
The first question that comes to mind is why would you ever need a table to adopt another table. There are 2 reasons that come to mind.
When you are loading huge amounts of data especially of a read only nature - its often convenient to not have that table be visible to your applications until
you are done with the loading process. So you may want to make it a child after the loading.
Your tables seemed fairly unrelated when you started out and then one day you realized you really were talking about apples and apples and need to report on them together at a higher level.
One situation like this to give a somewhat real-world perspective - lets say you developed a timesheet app for an organization and each department insisted on having their own version of the app and each along with
the basic fields needed to track some additional ones of their own. Then higher forces
came in and said I need to know what everyone is doing, but I don't need to see all that other crap they keep track of.. Two options come to mind - create a bunch of views
that union stuff together or institute a round-up-the-children-and-adopt-them program.
This fits into one of the categories of things that PostgreSQL lets you do that PgAdmin III doesn't have a graphical way to let you do it.
If you try to inherit in PgAdmin III from a table that already exists, that option is just greyed out. So you have to resort to DDL SQL statements. Luckily its fairly trivial. Well this really only works
for PostgreSQL 8.2+. I don't think PostgreSQL 8.1 and below supported INHERIT/NO INHERIT in the ALTER TABLE statement.