RustProof Labs: blogging for education (logo)

Build your Data Dictionary in PostgreSQL

By Ryan Lambert -- Published September 24, 2018

This post provides an introduction to building a data dictionary directly in our PostgreSQL databases. The steps outlined here are specific to PostgreSQL, though every database platform has these basics components in place.

This post is part of the series PostgreSQL: From Idea to Database.

What is a Data Dictionary?

Documentation is a good thing. Data dictionaries are one important component of documenting your databases. A data dictionary is a common tool used to provide database-specific documentation to analysts, developers, and other business users. A good data dictionary provides insights into a database's structure, constraints, relationships, and sources of data found inside a system.

Continue Reading

Geometry viewer added to pgAdmin 4

By Ryan Lambert -- Published September 13, 2018

pgAdmin4 recently added a fantastic new feature: Geometry viewer. This feature, available in PgAdmin 4 v3.3, allows you to see your PostGIS data in a separate tab. Use ST_Transform(geom, 4326) and it overlays your data on the OpenStreetMap background. Awesome!!

Boston GIS introduced the basics of how the Geometry Viewer functions. This post illustrates why I think this feature is so cool.

Why is geometry viewer awesome?

Spatial (PostGIS) data typically doesn't translate well in most SQL tools. pgAdmin's new geometry viewer provides a dead-simple way to visualize, verify and troubleshoot spatial data in their databases. This feature is targeted for database professionals who support PostGIS databases.

Continue Reading

From Idea to Database: Define, Design, Repeat

By Ryan Lambert -- Published September 07, 2018

The database is one of the most critical components of most software projects. Planning for your database project should not be taken lightly. Failure rates in IT projects are unfortunately high; much of that problem is due to a lack of proper planning and design. Most of the database anti-patterns I previously wrote about can be avoided by understanding the system you need to build and designing it appropriately.

This post uses the PiWS project to illustrate the Define and Design approach I use throughout the initial development phases of any new database project. Most projects go through multiple iterations of define and design.

This post is part of the series PostgreSQL: From Idea to Database. This series uses the PiWS project for our example project, to read more about the PiWS, see my introductory post.

Project management, not optional

Anyone who has worked on a project with more than one person to design and build anything has heard this: "I forgot to tell you..." followed by some various change requirements.

Continue Reading

Gotcha restoring XML data from pg_dump

By Ryan Lambert -- Published August 25, 2018

Recently, I ran into a problem when moving a database from a production PostGIS-enabled PostgreSQL database to a local development virtual machine. This database includes a table many QGIS users may have encountered: public.layer_styles. The public.layer_styles table is used by QGIS software to allow saving the styling used for PostGIS spatial layers. Style information is stored in XML format in a column named styleqml that includes a document type declaration (<!DOCTYPE>).

XML data with <!DOCTYPE> is at the core of this problem. If your PostGIS databases uses the public.layer_styles table, the workaround and other considerations will be helpful.

The bug: Invalid pg_dump file w/ XML

The problem encountered is filed under bug #15342. I like how Tom Lane summarized the issue:

"There are two problems here: pg_dump neglects to force a safe value of xmloption for the restore step, plus there doesn't seem to be a safe value for it to force :-(."

The rest of this post explores the factors involved with this problem.

Who does this affect?

If you have a PostGIS database with QGIS users storing style information in the public.layer_styles table, you are affected. More specifically, this affects PostgreSQL databases with columns using the XML data type storing XML data that includes <!DOCTYPE> blocks. These databases will experience headaches when restoring dump files saved using the pg_dump or pg_dumpall utilities.

The Check for XML columns section below provides a query to help determine if your databases are affected.

Continue Reading

PostgreSQL Crash Course

By Ryan Lambert -- Published July 30, 2018

PostgreSQL is a powerful, open-source relational database (RMDS). It is the database of choice here at RustProof Labs, I wrote previously why I chose PostgreSQL over MySQL. This post provides a 10-mile view of how PostgreSQL is structured, tools to use, and other important concepts. The goal is to provide a broad view of PostgreSQL with resources to more detail on each topic, without getting caught up in too much technical jargon.

This post is part of the series PostgreSQL: From Idea to Database.

Installing PostgreSQL

PostgreSQL will run on most any modern hardware and operating systems. PostgreSQL has installation guides for a large number of operating systems, supporting most Linux/Unix, Mac, and Windows. As for hardware, it will run on nearly anything modern, including a Raspberry Pi (ARM processor). See the PostgreSQL supported platforms for more details.

I have ran PostgreSQL on Linux servers of all sizes, from tiny to powerful. I previously wrote about load testing PostgreSQL that gives more detail about some of the systems it can run on and what type of performance to expect.

PostgreSQL Structure

Every RDMS has its own way to organize the elements required for a reliable database system. PostgreSQL has the following hierarchy to organize the required elements.

Cluster > Database > Schema > Table > Data

Data is stored as rows, contained in tables, within a schema, within a database, within a cluster. The PostgreSQL Concepts page provides a nice explanation of this overall structure.

Continue Reading