In general, the core PostgreSQL community doesn't latch onto GUI tools. That said, pgAdmin is one of the more popular GUI options for PostgreSQL. I hadn't used pgAdmin for quite a while (the transition from III to 4 was... rough), but a recent feature caught my eye: Geometry viewer.

This feature, available in pgAdmin 4 v3.3, allows you to see your PostGIS data in a separate tab directly in pgAdmin. When you execute a query that includes PostGIS data, pgAdmin overlays your data on the OpenStreetMap background in the geometry viewer.

Awesome!!

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

DBeaver now has a geometry viewer feature too!

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

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

Update: This bug is fixed in the latest supported version's minor releases. Upgrade to fix this 🐛 bug!

I ran into a problem when moving a database from a production PostGIS-enabled PostgreSQL server to a development server for testing. Turns out, what I found is a bug in pg_dump related to the XML data type. The problem encountered is filed under bug #15342. 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 what the problem is, how to tell if you are affected, and your options if you find yourself in this group.

• Who does this affect?
• Data to reproduce the bug
• Check for problematic XML data
• Workaround for part of the problem
• PostgreSQL and XML, and bug #15342
• What to do?

Continue Reading

PostgreSQL (postgres, pg) 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 successfully 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.

Structure and Terminology

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

Nearly every modern organization, large or small, has a significant amount of IT infrastructure reliant on 3rd parties. For the purpose of this post, 3rd parties includes anything you think of as "the cloud" (e.g. not my servers) or any service that you don't fully control on your hardware. Web hosting, email, social media, and file storage are all examples of services commonly outsourced.

I wrote previously about the responsibilities associated with managing data securely. A solid cybersecurity posture should require evaluating 3rd parties for proper security practices. This post provides a loose framework for evaluating a 3rd party's overall cybersecurity posture. This evaluation can then be used in your ongoing decision making to pick new vendors and reevaluate existing vendors. This post does not go in depth to any specific technology or vendor specific details, instead I intend to focus more on the culture of security.

Security as a feature

Security as a feature in this model has 3 overarching components:

• Preparation
• Problem solving
• Learn from mistakes

The quality of cybersecurity is an aggregate of these three components. An organization with good security practices is well prepared, knows how to react when something goes wrong, and they learn from their mistakes.

Plan for the worst, hope for the best.

The reality is, until more organizations make cybersecurity a higher priority, the never-ending stream of data breaches will continue.

Continue Reading