tag:blogger.com,1999:blog-36450707059546918072024-02-20T13:34:53.891+00:00Dave's Postgres BlogPostgres related musings and ramblingsDave Pagehttp://www.blogger.com/profile/10523190286514017933noreply@blogger.comBlogger88125tag:blogger.com,1999:blog-3645070705954691807.post-80631830273045611602020-11-16T16:13:00.012+00:002021-01-11T09:05:47.360+00:00macOS Big Sur upgrade breaking PostgreSQL installations<div><i><b>Update</b>: I, and others have subsequently been unable to reproduce the problem below. If you do run into the issue, please let me know in the comments below.</i></div><div><br /></div>Some of the readers of my blog may be aware that I'm responsible for the PostgreSQL Installers produced by EDB for the PostgreSQL Community. This includes both the <a href="https://www.postgresql.org/download/windows/">Windows</a> and <a href="https://www.postgresql.org/download/macosx/">macOS</a> installers, and the now-deprecated Linux installers (use RPMs/DEBs instead)!<div><br /></div><div>Over the weekend I upgraded my laptop to the new Big Sur release from Apple, a.k.a. macOS 11.0. Everything went just fine until I tried to connect to my PostgreSQL 13 development server and found it had failed to startup. I did some digging, and quickly found that there was a permissions problem:</div><div><br /></div><div><div><span style="font-family: courier; font-size: x-small;">dpage@hal:~$ sudo su - postgres -c '/Library/PostgreSQL/13/bin/postmaster -D/Library/PostgreSQL/13/data'</span></div><div><span style="font-family: courier; font-size: x-small;">2020-11-16 16:00:18.544 GMT [9532] FATAL: data directory "/Library/PostgreSQL/13/data" has invalid permissions</span></div><div><span style="font-family: courier; font-size: x-small;">2020-11-16 16:00:18.544 GMT [9532] DETAIL: Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).</span></div></div><div><span style="font-family: inherit;"><br /></span></div><div><span style="font-family: inherit;">Thankfully that's pretty easy to fix:</span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier; font-size: x-small;">sudo chmod 0700 /Library/PostgreSQL/13/data</span></div><div><span style="font-family: inherit;"><br /></span></div><div><span style="font-family: inherit;">LaunchDaemon then tried again to start PostgreSQL, and succeeded:</span></div><div><span style="font-family: courier;"><br /></span></div><div><div style="font-family: courier;"><span style="font-size: x-small;">dpage@hal:~$ ps -ef |grep postgres</span></div><div style="font-family: courier;"><span style="font-size: x-small;"> 503 9548 9547 0 4:02pm ?? 0:00.00 postgres: logger </span></div><div style="font-family: courier;"><span style="font-size: x-small;"> 503 9550 9547 0 4:02pm ?? 0:00.00 postgres: checkpointer </span></div><div style="font-family: courier;"><span style="font-size: x-small;"> 503 9551 9547 0 4:02pm ?? 0:00.00 postgres: background writer </span></div><div style="font-family: courier;"><span style="font-size: x-small;"> 503 9552 9547 0 4:02pm ?? 0:00.00 postgres: walwriter </span></div><div style="font-family: courier;"><span style="font-size: x-small;"> 503 9553 9547 0 4:02pm ?? 0:00.00 postgres: autovacuum launcher </span></div><div style="font-family: courier;"><span style="font-size: x-small;"> 503 9554 9547 0 4:02pm ?? 0:00.00 postgres: stats collector </span></div><div style="font-family: courier;"><span style="font-size: x-small;"> 503 9555 9547 0 4:02pm ?? 0:00.00 postgres: logical replication launcher </span></div><div style="font-family: courier;"><span style="font-size: x-small;"> 503 9556 9547 0 4:02pm ?? 0:00.03 postgres: postgres ml ::1(49777) idle </span></div><div style="font-family: courier;"><span style="font-size: x-small;"> 501 9558 9469 0 4:02pm ttys004 0:00.00 grep postgres</span></div><div style="font-family: courier; font-size: small;"><br /></div><div><span style="font-family: inherit;">So what happened here? It looks like the upgrade process for Big Sur "fixed" the permissions on the data directory to set them to what it thought was appropriate. Unfortunately, the new permissions are considered too broad by PostgreSQL which refuses to start to draw attention to the possible security issue.</span></div></div><div><span style="font-family: inherit;"><br /></span></div><div><span style="font-family: inherit;">Since correcting the permissions I've rebooted my machine and found that they stayed correct, so it's likely this issue happened during the upgrade process, and not as part of the boot process or some other process that periodically "fixes" permissions.</span></div><div><span style="font-family: inherit;"><br /></span></div><div><span style="font-family: inherit;">I've also tried installing PostgreSQL 12, and found that a fresh installation works as it should; the installer correctly sets the permissions on the data directory and PostgreSQL happily starts.</span></div><div><span style="font-family: inherit;"><br /></span></div><div><span style="font-family: inherit;">So, if you upgrade to Big Sur and PostgreSQL stops working, check that the upgrade process hasn't modified the permissions on the data directory. The directory should look like this:</span></div><div><span style="font-family: inherit;"><br /></span></div><div><div style="font-family: courier; font-size: small;">dpage@hal:~$ ls -al /Library/PostgreSQL/13 | grep data</div><div style="font-family: courier; font-size: small;">drwx------ 31 postgres daemon 992 16 Nov 16:02 data</div><div style="font-family: courier; font-size: small;"><br /></div><div><span style="font-family: inherit;">We will be raising this issue with Apple.</span></div></div>Dave Pagehttp://www.blogger.com/profile/10523190286514017933noreply@blogger.com0tag:blogger.com,1999:blog-3645070705954691807.post-24688181647403820402020-08-25T13:58:00.004+01:002021-01-11T10:59:58.308+00:00 Testing pgAdmin<p><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Like any software, pgAdmin has bugs. At the time of writing there are 119 new or in-progress issues in the </span><a href="https://redmine.postgresql.org/projects/pgadmin4/issues?utf8=%E2%9C%93&set_filter=1&sort=id%3Adesc&f%5B%5D=status_id&op%5Bstatus_id%5D=%3D&v%5Bstatus_id%5D%5B%5D=1&v%5Bstatus_id%5D%5B%5D=2&f%5B%5D=tracker_id&op%5Btracker_id%5D=%3D&v%5Btracker_id%5D%5B%5D=1&f%5B%5D=&c%5B%5D=tracker&c%5B%5D=status&c%5B%5D=priority&c%5B%5D=subject&c%5B%5D=assigned_to&c%5B%5D=updated_on&group_by=&t%5B%5D=" style="text-decoration-line: none;"><span style="color: #1155cc; font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; text-decoration-line: underline; text-decoration-skip-ink: none; vertical-align: baseline; white-space: pre-wrap;">bug tracker</span></a><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">, which is pretty good for a piece of software with so many moving parts, and regular-as-clockwork monthly releases. Over 10% of these issues are currently assigned to the reporter to gather further information, plus there will undoubtedly be some duplicates and support issues/feature requests in that number that haven’t yet been picked up in our weekly backlog refinement meeting and either closed or moved to the feature or support trackers.</span></p><span id="docs-internal-guid-1c3822c1-7fff-9128-0f1a-1a96bea0e433"><h1 dir="ltr" style="line-height: 1.38; margin-bottom: 6pt; margin-top: 20pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 400; vertical-align: baseline; white-space: pre-wrap;">Manual Testing</span></h1><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">We have one person, Fahar, who is permanently assigned as QA to the pgAdmin team at EDB, though he is also responsible for a number of other product releases such as the PostgreSQL installers for Windows and macOS (pgAdmin is the vast majority of his work however).</span></p><br /><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Fahar primarily does four things:</span></p><br /><ul style="margin-bottom: 0px; margin-top: 0px;"><li dir="ltr" style="font-family: arial; font-size: 11pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Works to verify that issues reported to users are reproducible, when they require complex setup or the developers have failed to reproduce them.</span></p></li><li dir="ltr" style="font-family: arial; font-size: 11pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Verifies that bugs that have been fixed by the developers actually do fix the reported problem. </span></p></li><li dir="ltr" style="font-family: arial; font-size: 11pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Smoke tests release builds; that is, ensures the packages can be installed and basic functionality works prior to public release.</span></p></li><li dir="ltr" style="font-family: arial; font-size: 11pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Tests new features once development is believed to be complete.</span></p></li></ul><h1 dir="ltr" style="line-height: 1.38; margin-bottom: 6pt; margin-top: 20pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 400; vertical-align: baseline; white-space: pre-wrap;">Automation</span></h1><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">All pgAdmin tests and builds are automated using Jenkins. The system will notify the development team of any failures via email and via a dedicated Slack channel (internal to EDB). There are also large screens mounted on the wall in development offices to show the status of the test and build runs in real time.</span></p><h2 dir="ltr" style="line-height: 1.38; margin-bottom: 6pt; margin-top: 18pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 400; vertical-align: baseline; white-space: pre-wrap;">Automated Testing</span></h2><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">We have three primary automated test suites for pgAdmin, which are essential in allowing us to release quickly and regularly, with minimal issues.</span></p><h3 dir="ltr" style="line-height: 1.38; margin-bottom: 4pt; margin-top: 16pt;"><span style="color: #434343; font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 400; vertical-align: baseline; white-space: pre-wrap;">Python Tests</span></h3><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">The Python tests (sometimes referred to as the API tests) are intended to test the Python application that implements the pgAdmin server. The test suite will instantiate the server and then make hundreds of calls to the server with different inputs to ensure itr responds as expected. A small number of these tests operate directly on the Python code, and do not run through the API (e.g. for testing server import/export).</span></p><br /><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">One of the Python tests is the RE-SQL (Reverse Engineered SQL) test. This is a special case as it’s a single test which is actually comprised of around 460 sub-tests, designed to test the SQL generation in pgAdmin; each sub-test will create or modify an object, and then validate that the SQL generated by pgAdmin is correct.</span></p><br /><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">The Python tests are run on every check in to the pgAdmin GIT repository. There are nearly 1200 tests (plus the 460 RE-SQL tests), which are run on Windows, macOS and Linux against all supported database servers (PostgreSQL 9.5 through 12, and EDB Postgres Advanced Server 9.5 through 12). </span><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 700; vertical-align: baseline; white-space: pre-wrap;">This equates to nearly 100,000 Python tests being run on every check in</span><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">. The exact number isn’t quite that because some tests only run on certain database server versions.</span></p><h3 dir="ltr" style="line-height: 1.38; margin-bottom: 4pt; margin-top: 16pt;"><span style="color: #434343; font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 400; vertical-align: baseline; white-space: pre-wrap;">Jasmine Tests</span></h3><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">The Jasmine tests are unit tests for the Javascript code used in the frontend user interface of pgAdmin, named after the framework that is used to run them. At the time of writing, there are 866 Javascript unit tests, each of which is run on Windows, macOS and Linux. That means that </span><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 700; vertical-align: baseline; white-space: pre-wrap;">2,598 Javascript unit tests are run on every check in</span><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">.</span></p><h3 dir="ltr" style="line-height: 1.38; margin-bottom: 4pt; margin-top: 16pt;"><span style="color: #434343; font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 400; vertical-align: baseline; white-space: pre-wrap;">Feature Tests</span></h3><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Feature tests are “full system” tests in which an instance of pgAdmin is started and loaded into a browser, and then inputs are automatically given to the browser to simulate end-to-end operation of the application, with the displayed output then being verified. The feature tests are run on a nightly basis as they take a long time to execute. They are run using </span><a href="https://aerokube.com/selenoid/" style="text-decoration-line: none;"><span style="color: #1155cc; font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; text-decoration-line: underline; text-decoration-skip-ink: none; vertical-align: baseline; white-space: pre-wrap;">Selenoid</span></a><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"> using Google Chrome as the browser (we plan to add Firefox in the future), and run against all supported database server versions.</span></p><br /><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">There are only around 15 feature tests in the suite (which actually runs from the Python test framework), however each test may actually verify anything from 1 to 20 or more individual pieces of functionality.</span></p><h3 dir="ltr" style="line-height: 1.38; margin-bottom: 4pt; margin-top: 16pt;"><span style="color: #434343; font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 400; vertical-align: baseline; white-space: pre-wrap;">Linting</span></h3><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Various tasks run as part of the automated tests to check the code formatting, including Javascript linting and </span><a href="https://www.python.org/dev/peps/pep-0008/" style="text-decoration-line: none;"><span style="color: #1155cc; font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; text-decoration-line: underline; text-decoration-skip-ink: none; vertical-align: baseline; white-space: pre-wrap;">Python PEP-8</span></a><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"> validation.</span></p><h2 dir="ltr" style="line-height: 1.38; margin-bottom: 6pt; margin-top: 18pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 400; vertical-align: baseline; white-space: pre-wrap;">Automated Builds</span></h2><h3 dir="ltr" style="line-height: 1.38; margin-bottom: 4pt; margin-top: 16pt;"><span style="color: #434343; font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 400; vertical-align: baseline; white-space: pre-wrap;">Test Builds</span></h3><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Test builds of all packages (Docker, tarballs, docs, RPMs, DEBs, installers etc) are made with every check in to the GIT repository. These are discarded after the build completes as their primary purpose is to verify that all the code builds without error on every platform, and that packages can be successfully generated.</span></p><h3 dir="ltr" style="line-height: 1.38; margin-bottom: 4pt; margin-top: 16pt;"><span style="color: #434343; font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 400; vertical-align: baseline; white-space: pre-wrap;">Snapshot Builds</span></h3><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><a href="https://www.postgresql.org/ftp/pgadmin/pgadmin4/snapshots/" style="text-decoration-line: none;"><span style="color: #1155cc; font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; text-decoration-line: underline; text-decoration-skip-ink: none; vertical-align: baseline; white-space: pre-wrap;">Snapshot builds</span></a><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"> are created on a nightly basis and made available for testing and use by anyone that is interested in contributing or needs a fix in a hurry.</span></p><h3 dir="ltr" style="line-height: 1.38; margin-bottom: 4pt; margin-top: 16pt;"><span style="color: #434343; font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 400; vertical-align: baseline; white-space: pre-wrap;">QA Builds</span></h3><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><a href="https://developer.pgadmin.org/builds/" style="text-decoration-line: none;"><span style="color: #1155cc; font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; text-decoration-line: underline; text-decoration-skip-ink: none; vertical-align: baseline; white-space: pre-wrap;">QA builds</span></a><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"> are produced on the Monday before one of the monthly releases. They are uploaded to a server from where Fahar will download and smoke test them to ensure there are no obvious show-stopping issues. These builds will also be tested to ensure they are carrying the latest version of certain dependencies, such as the OpenSSL libraries.</span></p><h1 dir="ltr" style="line-height: 1.38; margin-bottom: 6pt; margin-top: 20pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 400; vertical-align: baseline; white-space: pre-wrap;">Static Code Analysis</span></h1><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">EDB maintains a </span><a href="https://www.sonarqube.org/" style="text-decoration-line: none;"><span style="color: #1155cc; font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; text-decoration-line: underline; text-decoration-skip-ink: none; vertical-align: baseline; white-space: pre-wrap;">SonarQube</span></a><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"> server for performing static code analysis of all the product code within the company, including the Open Source pgAdmin code. This allows us to monitor the state of a number of key metrics that can be detected through analysis:</span></p><br /><ul style="margin-bottom: 0px; margin-top: 0px;"><li dir="ltr" style="font-family: arial; font-size: 11pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Bugs</span></p></li><li dir="ltr" style="font-family: arial; font-size: 11pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Security vulnerabilities</span></p></li><li dir="ltr" style="font-family: arial; font-size: 11pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Security hotspots (code that should be reviewed as it’s a possible source of a vulnerability)</span></p></li><li dir="ltr" style="font-family: arial; font-size: 11pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Code smells (code that looks badly structured or implemented)</span></p></li><li dir="ltr" style="font-family: arial; font-size: 11pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Code duplications</span></p></li></ul><br /><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">The current state of the pgAdmin code can be seen below:</span></p><br /><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt; text-align: center;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"><span style="border: none; display: inline-block; height: 720px; overflow: hidden; width: 602px;"><img height="720" src="https://lh3.googleusercontent.com/RB0WgyiwZMg0idsDt30uqv5P_RlP823j4pW-lq_K9hXYj8F3xu6OL2ZzvqoeYmwT63UeGoDZ74fx7RsLZ5wvWYp_pY_DnXsGrqijiZchpaKPffuunuRZyvqFH2K9JmZZZdqQaaOd" style="margin-left: 0px; margin-top: 0px;" width="602" /></span></span></p><br /><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Note that the sharp rise on July 19th was the result of a SonarQube upgrade that added/changed a number of the analysis rules.</span></p><br /><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">We’re in the process of working through the different issues, with focus currently on code smells (mostly overly complex functions that need to be refactored) and security hotspots (mostly regexps in the code). Each of the issues reported may or may not involve a code change - at the very least they will be reviewed and marked as “Won’t fix”.</span></p><h1 dir="ltr" style="line-height: 1.38; margin-bottom: 6pt; margin-top: 20pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 400; vertical-align: baseline; white-space: pre-wrap;">Developer Testing</span></h1><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">All the developers are required to test patches before they are submitted for inclusion in pgAdmin. This includes running all relevant existing tests (there’s no point in running Jasmine tests for a change that only touches Python code for example), and linting tests (e.g. the PEP-8 checks). They are also expected to include suitable tests (or updates to tests) to validate changes they’ve made.</span></p><br /><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Many of the developers also use static code analysis in their development environments, either the tools built directly into products like PyCharm, or personal SonarQube scans.</span></p><h1 dir="ltr" style="line-height: 1.38; margin-bottom: 6pt; margin-top: 20pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 400; vertical-align: baseline; white-space: pre-wrap;">Statistics</span></h1><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">There are various statistics we can look at as some form of code quality metric.</span></p><h2 dir="ltr" style="line-height: 1.38; margin-bottom: 6pt; margin-top: 18pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 400; vertical-align: baseline; white-space: pre-wrap;">Team</span></h2><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">The team size is interesting because it gives us an idea of how much each member of the team is responsible for on average.</span></p><br /><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">There are currently 6 people working on pgAdmin full time. In addition, we have a number of people at </span><a href="https://www.enterprisedb.com/" style="text-decoration-line: none;"><span style="color: #1155cc; font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; text-decoration-line: underline; text-decoration-skip-ink: none; vertical-align: baseline; white-space: pre-wrap;">EDB</span></a><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"> who are primarily focussed on </span><a href="https://www.enterprisedb.com/products/postgresql-enterprise-manager-best-gui-tools-database-management" style="text-decoration-line: none;"><span style="color: #1155cc; font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; text-decoration-line: underline; text-decoration-skip-ink: none; vertical-align: baseline; white-space: pre-wrap;">Postgres Enterprise Manager</span></a><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">, which as it is based on pgAdmin, occasionally means that that team will contribute to pgAdmin as well. We also have a number of community contributors who submit the occasional patch.</span></p><br /><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Counting only the full time developers, that’s </span><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 700; vertical-align: baseline; white-space: pre-wrap;">19.8 not-yet-fixed bugs per developer</span><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">.</span></p><h2 dir="ltr" style="line-height: 1.38; margin-bottom: 6pt; margin-top: 18pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 400; vertical-align: baseline; white-space: pre-wrap;">Users</span></h2><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">The number of users is interesting because the bigger the user base, the more likely we are to get issues reported.</span></p><br /><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Unfortunately, we don’t know how many users we have because pgAdmin is freely available. We do know that Docker is currently reporting over </span><a href="https://hub.docker.com/r/dpage/pgadmin4" style="text-decoration-line: none;"><span style="color: #1155cc; font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; text-decoration-line: underline; text-decoration-skip-ink: none; vertical-align: baseline; white-space: pre-wrap;">50 million pulls</span></a><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"> of the official pgAdmin container, and that’s almost certainly not where most people get their copy of pgAdmin from.</span></p><br /><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Counting only the Docker users, and assuming each one of them has pulled every one of the 34 releases since the first one uploaded there, that's 1.47 million users (yeah, OK, we definitely don’t have that many users, but still…). That works out at </span><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 700; vertical-align: baseline; white-space: pre-wrap;">0.000081 not-yet-fixed bugs per user</span><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">, though arguably this number is also pretty meaningless. A better one might be the number of bugs reported over the lifetime of the codebase (which is 1856, including issues in new features that ended up being fixed prior to release), which gives us </span><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 700; vertical-align: baseline; white-space: pre-wrap;">0.001263 reported and verified bugs per user</span><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">.</span></p><h2 dir="ltr" style="line-height: 1.38; margin-bottom: 6pt; margin-top: 18pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 400; vertical-align: baseline; white-space: pre-wrap;">Code</span></h2><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Looking at the code, we see the following stats:</span></p><br /><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">dpage@hal:~/git/pgadmin4$ perl ~/bin/cloc.pl .</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"> 3650 text files.</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"> 3148 unique files. </span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"> 1115 files ignored.</span></p><span style="font-family: courier;"><br /></span><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">https://github.com/AlDanial/cloc v 1.66 T=12.18 s (217.1 files/s, 27242.3 lines/s)</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">--------------------------------------------------------------------</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Language files blank comment code</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">--------------------------------------------------------------------</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Python 714 21204 30011 92424</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">JavaScript 285 10216 9984 75844</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">SQL 1384 5383 2889 38516</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">JSON 108 31 0 24752</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">SASS 34 995 16 5210</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">make 2 111 37 3717</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">CSS 39 376 66 2012</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">HTML 28 42 20 1548</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">XML 13 0 0 1208</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">C++ 9 342 247 1177</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Bourne Shell 12 298 204 1096</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Qt 3 0 0 945</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">DOS Batch 1 85 8 284</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">C/C++ Header 10 94 107 244</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Qt Project 1 25 7 99</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Objective C++ 1 2 11 14</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Windows Resource File 1 0 0 1</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">--------------------------------------------------------------------</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">SUM: 2645 39204 43607 249091</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: courier; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">--------------------------------------------------------------------</span></p><br /><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">So, there are a shade under a quarter of a million lines of code in pgAdmin, which equates to </span><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 700; vertical-align: baseline; white-space: pre-wrap;">one not-yet-fixed bug per 2,093 lines of code</span><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">. By way of comparison, this </span><a href="https://stackoverflow.com/a/56043694" style="text-decoration-line: none;"><span style="color: #1155cc; font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; text-decoration-line: underline; text-decoration-skip-ink: none; vertical-align: baseline; white-space: pre-wrap;">Stackoverflow answer</span></a><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"> cites a book that in turn cites a number of studies into industry average bug rates, which are believed to be in the order of 1-25 bugs per 1000 lines of code.</span></p><h1 dir="ltr" style="line-height: 1.38; margin-bottom: 6pt; margin-top: 20pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 400; vertical-align: baseline; white-space: pre-wrap;">Conclusion</span></h1><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">pgAdmin has a relatively small number of developers, but a large amount of code and number of users. From the data we have, we can assume that with such a large user base, bugs are more likely to be reported than if there was a user base of tens or hundreds of users. Despite this, the number of issues per 1000 lines of code seems to be very low in comparison to industry studies.</span></p><br /><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Despite the huge amount of effort that goes into automated testing and builds, there will always be bugs. We rely on users to give us feedback when they find issues, and aim to fix everything as soon as possible, prioritising based on impact and likelihood of any given user running into each issue. We also try to balance time spent on bug fixes with new features and housekeeping tasks (aimed at keeping the code maintainable).</span></p><br /><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">None of this would be possible without the automation in use for building and testing the code, or without the hard working developers (including Fahar) and users that report and help them fix issues, to whom I owe a huge debt of gratitude.</span></p><div><span style="font-family: Arial; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"><br /></span></div></span>Dave Pagehttp://www.blogger.com/profile/10523190286514017933noreply@blogger.com0tag:blogger.com,1999:blog-3645070705954691807.post-60902644057061530232020-06-25T15:32:00.001+01:002020-06-25T15:32:50.738+01:00system_stats extension for PostgreSQL<div dir="ltr" style="text-align: left;" trbidi="on">
One of my colleagues at <a href="https://www.enterprisedb.com/">EDB</a> spent some time working on a new extension for PostgreSQL and EDB Postgres Advanced Server as part of a proof of concept that I asked him to look into. I'm pleased to say that we've decided to make that work Open Source, releasing it under the PostgreSQL licence.<br />
<br />
The <a href="https://github.com/EnterpriseDB/system_stats">system_stats extension</a> offers a number of stored procedures that are useful when monitoring Postgres. They expose various system metrics to the database server, allowing you to extend your database monitoring to include information about CPU, memory, disk and network usage without having to add a separate mechanism to gain access to that information.<br />
<br />
A number of functions are included:<br />
<br />
<b>pg_sys_os_info()</b><br />
This interface allows the user to get operating system statistics.<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">postgres=# SELECT * FROM pg_sys_os_info();</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">-[ RECORD 1 ]-------+--------------------------------------------------------------------------------------------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">name | Darwin</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">version | Darwin Kernel Version 19.4.0: Wed Mar 4 22:28:40 PST 2020; root:xnu-6153.101.6~15/RELEASE_X86_64</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">host_name | hal.local</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">domain_name | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">handle_count | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">process_count | 531</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">thread_count | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">architecture | x86_64</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">last_bootup_time | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">os_up_since_seconds | 281466</span><br />
<br />
<b>pg_sys_cpu_info()</b><br />
This interface allows the user to get CPU information.<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">postgres=# SELECT * FROM pg_sys_cpu_info();</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">-[ RECORD 1 ]------+---------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">vendor | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">description | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">model_name | MacBookPro15,1</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">processor_type | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">logical_processor | 12</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">physical_processor | 6</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">no_of_cores | 12</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">architecture | x86_64</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">clock_speed_hz | 2900000000</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">cpu_type | 7</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">cpu_family | 260141638</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">byte_order | 1234</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">l1dcache_size | 32</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">l1icache_size | 32</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">l2cache_size | 256</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">l3cache_size | 12288</span><br />
<br />
<b>pg_sys_cpu_usage_info()</b><br />
This interface allows the user to get CPU usage information. Values are a percentage of time spent by CPUs for all operations.<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">postgres=# SELECT * FROM pg_sys_cpu_usage_info();</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">-[ RECORD 1 ]-------------------+----------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">usermode_normal_process_percent | 1.6806724</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">usermode_niced_process_percent | 0</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">kernelmode_process_percent | 0</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">idle_mode_percent | 98.31933</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">io_completion_percent | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">servicing_irq_percent | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">servicing_softirq_percent | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">user_time_percent | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">processor_time_percent | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">privileged_time_percent | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">interrupt_time_percent | </span><br />
<br />
<b>pg_sys_memory_info()</b><br />
This interface allows the user to get memory usage information. All the values are in bytes.<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">postgres=# SELECT * FROM pg_sys_memory_info();</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">-[ RECORD 1 ]----+------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">total_memory | 34359738368</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">used_memory | 31668727808</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">free_memory | 2691010560</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">swap_total | 3221225472</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">swap_used | 2672033792</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">swap_free | 549191680</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">cache_total | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">kernel_total | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">kernel_paged | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">kernel_non_paged | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">total_page_file | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">avail_page_file |</span><br />
<br />
<b>pg_sys_io_analysis_info()</b><br />
This interface allows the user to get an I/O analysis of block devices.<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">postgres=# SELECT * FROM pg_sys_io_analysis_info();</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">-[ RECORD 1 ]-+-------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">device_name | disk0</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">total_reads | 9044867</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">total_writes | 6688535</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">read_bytes | 127989366784</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">write_bytes | 127197491200</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">read_time_ms | 3270007</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">write_time_ms | 3959392</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">-[ RECORD 2 ]-+-------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">device_name | disk2</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">total_reads | 2222011</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">total_writes | 3213728</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">read_bytes | 25915008512</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">write_bytes | 99796177408</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">read_time_ms | 22302918</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">write_time_ms | 65947889</span><br />
<br />
<b>pg_sys_disk_info()</b><br />
This interface allows the user to get the disk information.<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">postgres=# SELECT * FROM pg_sys_disk_info();</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">-[ RECORD 1 ]----+---------------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">mount_point | /</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">file_system | apfs</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">drive_letter | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">drive_type | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">file_system_type | /dev/disk1s5</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">total_space | 2000796545024</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">used_space | 11124047872</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">free_space | 1328550260736</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">total_inodes | 19539028760</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">used_inodes | 487630</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">free_inodes | 19538541130</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">-[ RECORD 2 ]----+---------------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">mount_point | /System/Volumes/Data</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">file_system | apfs</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">drive_letter | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">drive_type | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">file_system_type | /dev/disk1s1</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">total_space | 2000796545024</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">used_space | 11124047872</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">free_space | 1328550260736</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">total_inodes | 19539028760</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">used_inodes | 487630</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">free_inodes | 19538541130</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">-[ RECORD 3 ]----+---------------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">mount_point | /private/var/vm</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">file_system | apfs</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">drive_letter | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">drive_type | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">file_system_type | /dev/disk1s4</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">total_space | 2000796545024</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">used_space | 11124047872</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">free_space | 1328550260736</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">total_inodes | 19539028760</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">used_inodes | 487630</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">free_inodes | 19538541130</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">-[ RECORD 4 ]----+---------------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">mount_point | /Volumes/Backup</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">file_system | hfs</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">drive_letter | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">drive_type | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">file_system_type | /dev/disk3</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">total_space | 2000796545024</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">used_space | 11124047872</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">free_space | 1328550260736</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">total_inodes | 19539028760</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">used_inodes | 487630</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">free_inodes | 19538541130</span><br />
<br />
<b>pg_sys_load_avg_info()</b><br />
This interface allows the user to get the average load of the system over 1, 5, 10 and 15 minute intervals.<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">postgres=# SELECT * FROM pg_sys_load_avg_info();</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">-[ RECORD 1 ]------------+----------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">load_avg_one_minute | 1.4116211</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">load_avg_five_minutes | 1.5273438</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">load_avg_ten_minutes | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">load_avg_fifteen_minutes | 1.5668945</span><br />
<br />
<b>pg_sys_process_info()</b><br />
This interface allows the user to get process information.<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">postgres=# SELECT * FROM pg_sys_process_info();</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">-[ RECORD 1 ]------+----</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">total_processes | 531</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">running_processes | 528</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">sleeping_processes | 0</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">stopped_processes | 0</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">zombie_processes | 2</span><br />
<br />
<b>pg_sys_network_info()</b><br />
This interface allows the user to get network interface information.<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">postgres=# SELECT * FROM pg_sys_network_info();</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">-[ RECORD 1 ]---+-------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">interface_name | lo0</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">ip_address | 127.0.0.1</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">tx_bytes | 281804800</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">tx_packets | 882164</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">tx_errors | 0</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">tx_dropped | 0</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">rx_bytes | 281804800</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">rx_packets | 882164</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">rx_errors | 0</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">rx_dropped | 0</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">link_speed_mbps | 0</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">-[ RECORD 2 ]---+-------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">interface_name | en0</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">ip_address | 192.168.0.2</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">tx_bytes | 3770546176</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">tx_packets | 13311181</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">tx_errors | 1164</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">tx_dropped | 0</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">rx_bytes | 7829563392</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">rx_packets | 9070534</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">rx_errors | 0</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">rx_dropped | 0</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">link_speed_mbps | 0</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">-[ RECORD 3 ]---+-------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">interface_name | utun4</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">ip_address | 172.24.64.89</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">tx_bytes | 5830656</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">tx_packets | 27350</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">tx_errors | 0</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">tx_dropped | 0</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">rx_bytes | 75328512</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">rx_packets | 124340</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">rx_errors | 0</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">rx_dropped | 0</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">link_speed_mbps | 0</span><br />
<br />
<b>pg_sys_cpu_memory_by_process()</b><br />
This interface allows the user to get the CPU and memory information for each process ID.<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">postgres=# SELECT * FROM pg_sys_cpu_memory_by_process();</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">-[ RECORD 1 ]---------+-----------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">pid | 62633</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">name | postgres</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">running_since_seconds | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">cpu_usage | 4.48</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">memory_usage | 0.02</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">memory_bytes | 6848512</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">-[ RECORD 2 ]---------+-----------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">pid | 62632</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">name | psql</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">running_since_seconds | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">cpu_usage | 0</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">memory_usage | 0.01</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">memory_bytes | 2453504</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">-[ RECORD 3 ]---------+-----------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">pid | 62594</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">name | Google Chrome He</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">running_since_seconds | </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">cpu_usage | 0</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">memory_usage | 0.09</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">memory_bytes | 31694848</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">...</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">...</span><br />
<br />
<div style="text-align: left;">
All functions are restricted for use by superusers and members of the <span style="font-family: Courier New, Courier, monospace;">monitor_system_stats </span><span style="font-family: inherit;">role due to the potentially sensitive nature of the information they make available. To grant a user permission to use the extension, grant them that role. For example:</span></div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">GRANT monitor_system_stats TO nagios;</span><br />
<br />
The extension is currently supported on Linux, macOS and Windows, though not all statistics are available or relevant on all platforms, so you may see some NULL values being returned.<br />
<br />
Pull Requests for additional platform support, additional functions, or bug fixes are welcome, as are bug reports.<br />
<br />
You can find the <a href="https://github.com/EnterpriseDB/system_stats">code</a> and a <a href="https://github.com/EnterpriseDB/system_stats/releases/tag/v1.0">source release</a> on Github; please see the <a href="https://github.com/EnterpriseDB/system_stats/blob/master/README.md">README</a> for more information. Devrim Gündüz has also made RPMs available on <a href="https://yum.postgresql.org/">yum.postgresql.org</a>.</div>
Dave Pagehttp://www.blogger.com/profile/10523190286514017933noreply@blogger.com0tag:blogger.com,1999:blog-3645070705954691807.post-65377206379758302742019-07-10T13:33:00.002+01:002020-05-27T13:24:40.153+01:00Reverse Proxying to pgAdmin<div dir="ltr" style="text-align: left;" trbidi="on">
Reverse proxying requests to a pgAdmin server is becoming more and more popular if posts to the mailing lists are to be taken as an indicative measure; more often than not when using <a href="https://cloud.docker.com/u/dpage/repository/docker/dpage/pgadmin4">pgAdmin in a container</a> (of which there have now been over 10 <i>million</i> pulls)! Typically users will deploy a reverse proxy for a couple of reasons; to host multiple applications in different subdirectories under the same domain, or to add SSL/TLS support independently of the application.<br />
<br />
Because of the number of questions asked, I spent a little time over the last couple of days doing some testing and updating the <a href="https://www.pgadmin.org/docs/pgadmin4/dev/container_deployment.html#reverse-proxying">documentation</a> with some examples. Here's a blog-ified version of that work.<br />
<h2 style="text-align: left;">
<br /></h2>
<h2 style="text-align: left;">
Nginx</h2>
<div>
<a href="https://nginx.org/">Nginx</a> is winning the battle of the web servers these days, beating out <a href="https://www.lighttpd.net/">Lighttpd</a> (which is still a fine piece of software) and the ageing and arguably bloated Apache <a href="http://httpd.apache.org/">HTTPD</a>. All of these servers support reverse proxying, and whilst I've looked at Nginx, the examples shown below can easily be translated to the other servers if you prefer to run them instead.</div>
<div>
<br /></div>
<div>
In the following examples, we have pgAdmin running in a Docker container (in which it's hosted under <a href="https://gunicorn.org/">Gunicorn</a>). For simplicity, the examples have Nginx running on the host machine, but it can also be easily run in another container, sharing a Docker network with pgAdmin. In such a configuration there is no need to map the pgAdmin container port to the host.</div>
<div>
<br /></div>
<div>
The container is launched as shown below. See the <a href="https://www.pgadmin.org/docs/pgadmin4/dev/container_deployment.html">documentation</a> for information on other useful environment variables you can set and paths you can map.</div>
<div>
<br /></div>
<div>
The commands below will pull the latest version of the container from the repository, and run it with port 5050 on the host mapped to port 80 on the container. It will set the default username and password to <i>user@domain.com</i> and <i>SuperSecret</i> respectively.</div>
<div>
<br /></div>
<div>
<pre style="-webkit-font-smoothing: antialiased; background-color: #effbfc; border-color: rgba(64, 128, 144, 0.33) rgba(64, 128, 144, 0.33) rgba(64, 128, 144, 0.33) rgba(64, 128, 144, 0.52); border-image: initial; border-style: solid; border-width: 1px 1px 1px 5px; color: #295c85; font-size: 13px; line-height: 18.2px; overflow: auto hidden; padding: 15px 0px 15px 15px;">docker pull dpage/pgadmin4
docker run -p <span class="m" style="color: #208050;">5050</span>:80 <span class="se" style="color: #4070a0; font-weight: bold;">\</span>
-e <span class="s2" style="color: #4070a0;">"PGADMIN_DEFAULT_EMAIL=user@domain.com"</span> <span class="se" style="color: #4070a0; font-weight: bold;">\</span>
-e <span class="s2" style="color: #4070a0;">"PGADMIN_DEFAULT_PASSWORD=SuperSecret"</span> <span class="se" style="color: #4070a0; font-weight: bold;">\</span>
-d dpage/pgadmin4</pre>
</div>
<div>
<br /></div>
<div>
A simple configuration to reverse proxy with Nginx to pgAdmin at the root directory looks like this:</div>
<div>
<br /></div>
<div>
<pre style="-webkit-font-smoothing: antialiased; background-color: #effbfc; border-color: rgba(64, 128, 144, 0.33) rgba(64, 128, 144, 0.33) rgba(64, 128, 144, 0.33) rgba(64, 128, 144, 0.52); border-image: initial; border-style: solid; border-width: 1px 1px 1px 5px; color: #295c85; font-size: 13px; line-height: 18.2px; overflow: auto hidden; padding: 15px 0px 15px 15px;"><span class="k" style="color: #007020; font-weight: bold;">server</span> <span class="p">{</span>
<span class="kn" style="color: #007020; font-weight: bold;">listen</span> <span class="mi" style="color: #208050;">80</span><span class="p">;</span>
<span class="kn" style="color: #007020; font-weight: bold;">server_name</span> <span class="s" style="color: #4070a0;">_</span><span class="p">;</span>
<span class="kn" style="color: #007020; font-weight: bold;">location</span> <span class="s" style="color: #4070a0;">/</span> <span class="p">{</span>
<span class="kn" style="color: #007020; font-weight: bold;">proxy_set_header</span> <span class="s" style="color: #4070a0;">Host</span> <span class="nv" style="color: #bb60d5;">$host</span><span class="p">;</span>
<span class="kn" style="color: #007020; font-weight: bold;">proxy_pass</span> <span class="s" style="color: #4070a0;">http://localhost:5050/</span><span class="p">;</span>
<span class="kn" style="color: #007020; font-weight: bold;">proxy_redirect</span> <span class="no" style="color: #60add5;">off</span><span class="p">;</span>
<span class="p">}</span>
<span class="p">}</span></pre>
</div>
<div>
<br /></div>
<div>
Here we tell Nginx to listen on port 80, and respond to any server name (sent by the client in the <i>Host</i> header). We then specify that all requests under the root directory are proxied back to port 5050 on the local host, and that the <i>Host</i> header is passed along as well. The <i>proxy_redirect</i> option tells the server not to rewrite the <i>Location</i> header.</div>
<div>
<br /></div>
<div>
But what if we want to host pgAdmin under a subdirectory, say <i>/pgadmin4</i>? In this case we need to change the path at the top of the <i>location</i> block <b>and</b> add the <i>X-Script-Name</i> header to the requests made to the pgAdmin container to tell it what subdirectory it's hosted under. This is shown below:</div>
<div>
<br /></div>
<div>
<pre style="-webkit-font-smoothing: antialiased; background-color: #effbfc; border-color: rgba(64, 128, 144, 0.33) rgba(64, 128, 144, 0.33) rgba(64, 128, 144, 0.33) rgba(64, 128, 144, 0.52); border-image: initial; border-style: solid; border-width: 1px 1px 1px 5px; color: #295c85; font-size: 13px; line-height: 18.2px; overflow: auto hidden; padding: 15px 0px 15px 15px;"><span class="k" style="color: #007020; font-weight: bold;">server</span> <span class="p">{</span>
<span class="kn" style="color: #007020; font-weight: bold;">listen</span> <span class="mi" style="color: #208050;">80</span><span class="p">;</span>
<span class="kn" style="color: #007020; font-weight: bold;">server_name</span> <span class="s" style="color: #4070a0;">_</span><span class="p">;</span>
<span class="kn" style="color: #007020; font-weight: bold;">location</span> <span class="s" style="color: #4070a0;">/pgadmin4/</span> <span class="p">{</span>
<span class="kn" style="color: #007020; font-weight: bold;">proxy_set_header</span> <span class="s" style="color: #4070a0;">X-Script-Name</span> <span class="s" style="color: #4070a0;">/pgadmin4</span><span class="p">;</span>
<span class="kn" style="color: #007020; font-weight: bold;">proxy_set_header</span> <span class="s" style="color: #4070a0;">Host</span> <span class="nv" style="color: #bb60d5;">$host</span><span class="p">;</span>
<span class="kn" style="color: #007020; font-weight: bold;">proxy_pass</span> <span class="s" style="color: #4070a0;">http://localhost:5050/</span><span class="p">;</span>
<span class="kn" style="color: #007020; font-weight: bold;">proxy_redirect</span> <span class="no" style="color: #60add5;">off</span><span class="p">;</span>
<span class="p">}</span>
<span class="p">}</span></pre>
</div>
<div>
<br /></div>
<div>
OK, so that's cool but we're talking about super top secret database stuffs here. It needs to be encrypted! Adding SSL/TLS support to the configuration is largely unrelated to pgAdmin itself, <b>except</b> that as with the subdirectory, we need to tell it the URL scheme (http or https) to use. We do this by setting the <i>X-Scheme</i> header. The other changes are to add a redirect from http to https, and to configure SSL/TLS:</div>
<div>
<br /></div>
<div>
<pre style="-webkit-font-smoothing: antialiased; background-color: #effbfc; border-color: rgba(64, 128, 144, 0.33) rgba(64, 128, 144, 0.33) rgba(64, 128, 144, 0.33) rgba(64, 128, 144, 0.52); border-image: initial; border-style: solid; border-width: 1px 1px 1px 5px; color: #295c85; font-size: 13px; line-height: 18.2px; overflow: auto hidden; padding: 15px 0px 15px 15px;"><span class="k" style="color: #007020; font-weight: bold;">server</span> <span class="p">{</span>
<span class="kn" style="color: #007020; font-weight: bold;">listen</span> <span class="mi" style="color: #208050;">80</span><span class="p">;</span>
<span class="kn" style="color: #007020; font-weight: bold;">return</span> <span class="mi" style="color: #208050;">301</span> <span class="s" style="color: #4070a0;">https://</span><span class="nv" style="color: #bb60d5;">$host$request_uri</span><span class="p">;</span>
<span class="p">}</span>
<span class="k" style="color: #007020; font-weight: bold;">server</span> <span class="p">{</span>
<span class="kn" style="color: #007020; font-weight: bold;">listen</span> <span class="mi" style="color: #208050;">443</span><span class="p">;</span>
<span class="kn" style="color: #007020; font-weight: bold;">server_name</span> <span class="s" style="color: #4070a0;">_</span><span class="p">;</span>
<span class="kn" style="color: #007020; font-weight: bold;">ssl_certificate</span> <span class="s" style="color: #4070a0;">/etc/nginx/server.crt</span><span class="p">;</span>
<span class="kn" style="color: #007020; font-weight: bold;">ssl_certificate_key</span> <span class="s" style="color: #4070a0;">/etc/nginx/server.key</span><span class="p">;</span>
<span class="kn" style="color: #007020; font-weight: bold;">ssl</span> <span class="no" style="color: #60add5;">on</span><span class="p">;</span>
<span class="kn" style="color: #007020; font-weight: bold;">ssl_session_cache</span> <span class="n">builtin</span><span class="p">:</span><span class="mi" style="color: #208050;">1000</span> <span class="s" style="color: #4070a0;">shared:SSL:10m</span><span class="p">;</span>
<span class="kn" style="color: #007020; font-weight: bold;">ssl_protocols</span> <span class="s" style="color: #4070a0;">TLSv1</span> <span class="s" style="color: #4070a0;">TLSv1.1</span> <span class="s" style="color: #4070a0;">TLSv1.2</span><span class="p">;</span>
<span class="kn" style="color: #007020; font-weight: bold;">ssl_ciphers</span> <span class="s" style="color: #4070a0;">HIGH:!aNULL:!eNULL:!EXPORT:!CAMELLIA:!DES:!MD5:!PSK:!RC4</span><span class="p">;</span>
<span class="kn" style="color: #007020; font-weight: bold;">ssl_prefer_server_ciphers</span> <span class="no" style="color: #60add5;">on</span><span class="p">;</span>
<span class="kn" style="color: #007020; font-weight: bold;">location</span> <span class="s" style="color: #4070a0;">/pgadmin4/</span> <span class="p">{</span>
<span class="kn" style="color: #007020; font-weight: bold;">proxy_set_header</span> <span class="s" style="color: #4070a0;">X-Script-Name</span> <span class="s" style="color: #4070a0;">/pgadmin4</span><span class="p">;</span>
<span class="kn" style="color: #007020; font-weight: bold;">proxy_set_header</span> <span class="s" style="color: #4070a0;">X-Scheme</span> <span class="nv" style="color: #bb60d5;">$scheme</span><span class="p">;</span>
<span class="kn" style="color: #007020; font-weight: bold;">proxy_set_header</span> <span class="s" style="color: #4070a0;">Host</span> <span class="nv" style="color: #bb60d5;">$host</span><span class="p">;</span>
<span class="kn" style="color: #007020; font-weight: bold;">proxy_pass</span> <span class="s" style="color: #4070a0;">http://localhost:5050/</span><span class="p">;</span>
<span class="kn" style="color: #007020; font-weight: bold;">proxy_redirect</span> <span class="no" style="color: #60add5;">off</span><span class="p">;</span>
<span class="p">}</span>
<span class="p">}</span></pre>
</div>
<h2 style="text-align: left;">
<br /></h2>
<h2 style="text-align: left;">
Traefik</h2>
<div>
<a href="https://traefik.io/">Traefik</a> is becoming increasingly popular in containerised environments because it's designed to integrate with the orchestration platform being used and auto-configure itself as much as possible. The examples below show it working with plain Docker, but it will work with Swarm, Compose, Kubernetes and other orchestrators as well. Note that Traefik is designed purely for reverse proxying, routing and load balancing; it's not a general purpose webserver.</div>
<div>
<br /></div>
<div>
In the examples below, the following Traefik configuration is used. Because it auto-configures itself, the changes we make to get the desired configuration are to the way the pgAdmin container is deployed, not to the Traefik configuration:</div>
<div>
<br /></div>
<div>
<pre style="-webkit-font-smoothing: antialiased; background-color: #effbfc; border-color: rgba(64, 128, 144, 0.33) rgba(64, 128, 144, 0.33) rgba(64, 128, 144, 0.33) rgba(64, 128, 144, 0.52); border-image: initial; border-style: solid; border-width: 1px 1px 1px 5px; color: #295c85; font-size: 13px; line-height: 18.2px; overflow: auto hidden; padding: 15px 0px 15px 15px;"><span class="na" style="color: #4070a0;">defaultEntryPoints</span> <span class="o" style="color: #666666;">=</span> <span class="s" style="color: #4070a0;">["http", "https"]</span>
<span class="k" style="color: #007020; font-weight: bold;">[entryPoints]</span>
<span class="k" style="color: #007020; font-weight: bold;">[entryPoints.http]</span>
<span class="na" style="color: #4070a0;">address</span> <span class="o" style="color: #666666;">=</span> <span class="s" style="color: #4070a0;">":80"</span>
<span class="s" style="color: #4070a0;"> [entryPoints.http.redirect]</span>
<span class="s" style="color: #4070a0;"> entryPoint = "https"</span>
<span class="s" style="color: #4070a0;"> [entryPoints.https]</span>
<span class="s" style="color: #4070a0;"> address = ":443"</span>
<span class="s" style="color: #4070a0;"> [entryPoints.https.tls]</span>
<span class="k" style="color: #007020; font-weight: bold;">[docker]</span>
<span class="na" style="color: #4070a0;">domain</span> <span class="o" style="color: #666666;">=</span> <span class="s" style="color: #4070a0;">"domain_name"</span>
<span class="na" style="color: #4070a0;">watch</span> <span class="o" style="color: #666666;">=</span> <span class="s" style="color: #4070a0;">true</span></pre>
</div>
<div>
<br /></div>
<div>
With this configuration, Traefik will automatically detect containers as they are launched, and reverse proxy to them using the virtual hostname generated from the container name and the domain in its config file, e.g. <i><container_name>.<domain></i>. SSL/TLS is enabled in this setup, with a redirect from plain http to https. The certificate used will be the default one built into Traefik; see the documentation for details on how <a href="https://docs.traefik.io/configuration/acme/">Let's Encrypt</a> or <a href="https://docs.traefik.io/configuration/entrypoints/#tls">certificates from other issuers</a> can be used.</div>
<div>
<br /></div>
<div>
To host pgAdmin at the root directory, we simply launch a container with the correct name, and no host to container port mapping:</div>
<div>
<br /></div>
<div>
<pre style="-webkit-font-smoothing: antialiased; background-color: #effbfc; border-color: rgba(64, 128, 144, 0.33) rgba(64, 128, 144, 0.33) rgba(64, 128, 144, 0.33) rgba(64, 128, 144, 0.52); border-image: initial; border-style: solid; border-width: 1px 1px 1px 5px; color: #295c85; font-size: 13px; line-height: 18.2px; overflow: auto hidden; padding: 15px 0px 15px 15px;">docker pull dpage/pgadmin4
docker run --name <span class="s2" style="color: #4070a0;">"pgadmin4"</span> <span class="se" style="color: #4070a0; font-weight: bold;">\</span>
-e <span class="s2" style="color: #4070a0;">"PGADMIN_DEFAULT_EMAIL=user@domain.com"</span> <span class="se" style="color: #4070a0; font-weight: bold;">\</span>
-e <span class="s2" style="color: #4070a0;">"PGADMIN_DEFAULT_PASSWORD=SuperSecret"</span> <span class="se" style="color: #4070a0; font-weight: bold;">\</span>
-d dpage/pgadmin4</pre>
</div>
<div>
<br /></div>
<div>
With the configuration and commands above, Traefik will host pgAdmin at <i>https://pgadmin4.domain_name/</i>. Of course, the domain name should be changed to a real one, and a suitable CNAME, A or AAAA record should be added to the DNS zone file.</div>
<div>
<br /></div>
<div>
In order to host pgAdmin under a subdirectory, as in the Nginx example we need to tell both the proxy server and pgAdmin about the subdirectory. We tell pgAdmin by setting the <i>SCRIPT_NAME</i> environment variable, and we tell Traefik by adding a label to the container instance. For example:</div>
<div>
<br /></div>
<div>
<pre style="-webkit-font-smoothing: antialiased; background-color: #effbfc; border-color: rgba(64, 128, 144, 0.33) rgba(64, 128, 144, 0.33) rgba(64, 128, 144, 0.33) rgba(64, 128, 144, 0.52); border-image: initial; border-style: solid; border-width: 1px 1px 1px 5px; color: #295c85; font-size: 13px; line-height: 18.2px; overflow: auto hidden; padding: 15px 0px 15px 15px;">docker pull dpage/pgadmin4
docker run --name <span class="s2" style="color: #4070a0;">"pgadmin4"</span> <span class="se" style="color: #4070a0; font-weight: bold;">\</span>
-e <span class="s2" style="color: #4070a0;">"PGADMIN_DEFAULT_EMAIL=user@domain.com"</span> <span class="se" style="color: #4070a0; font-weight: bold;">\</span>
-e <span class="s2" style="color: #4070a0;">"PGADMIN_DEFAULT_PASSWORD=SuperSecret"</span> <span class="se" style="color: #4070a0; font-weight: bold;">\</span>
-e <span class="s2" style="color: #4070a0;">"SCRIPT_NAME=/pgadmin4"</span> <span class="se" style="color: #4070a0; font-weight: bold;">\</span>
-l <span class="s2" style="color: #4070a0;">"traefik.frontend.rule=PathPrefix:/pgadmin4"</span> <span class="se" style="color: #4070a0; font-weight: bold;">\</span>
-d dpage/pgadmin4</pre>
</div>
<h2 style="text-align: left;">
<br /></h2>
<h2 style="text-align: left;">
Conclusion</h2>
<div>
Users are using reverse proxy servers to provide an interface between their clients and the pgAdmin server. These can be more traditional servers such as Nginx, or purpose designed reverse proxy servers such as Traefik. In either case, it's simple to configure SSL/TLS support or to host pgAdmin in a subdirectory.</div>
<div>
<br /></div>
</div>
Dave Pagehttp://www.blogger.com/profile/10523190286514017933noreply@blogger.com5tag:blogger.com,1999:blog-3645070705954691807.post-57450901532940515862019-06-30T16:28:00.001+01:002020-05-27T13:24:52.101+01:00Indexing Documents for Full Text Search<div dir="ltr" style="text-align: left;" trbidi="on">
During a conversation with $BOSS a couple of days ago the topic of the new Full Text Search in MongoDB came up. He (knowing of, but presumably having never worked with FTS in PostgreSQL) suggested I might create a tool like the one the MongoDB guys were proudly showing off in their keynote video from their recent conference to make it easy to index and search HTML documents on disk. I looked at him with a slight tilt of the head and remarked that it's already easy to do that, and just takes a few lines of Python code or similar.<br />
<br />
So, here I am on my flight back to the UK having written 36 lines of code whilst munching on my breakfast and enjoying my free seat upgrade. I should probably note that those 36 lines of code include import statements, comments, blank lines for readability, and a nicely formatted 7 line SQL query that could easily be just one line. Plus, the vast majority of the code is really just boilerplate to read a directory of files and extract their contents.<br />
<br />
Anyway, before I can go into that, it was necessary to first create a schema to hold the data we'd need to query. This is basically just a table to hold whatever information you like about each document, with a <i>tsvector</i> column for searching. For my test I decided to make the path to the file the primary key, and to store the title of the HTML document and the text in the body. The body text isn't really needed, but it does make it a touch easier to use a trigger to automatically maintain the tsvector data - plus of course, I could also then use that column and get rid of the source documents.<br />
<br />
Here's what the table looked like:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"> CREATE TABLE docs</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> (</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> path text NOT NULL,</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> title text NOT NULL,</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> body text NOT NULL,</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> tsv tsvector NOT NULL,</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> PRIMARY KEY (path)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> );</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"> CREATE INDEX docs_tsv_idx</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> ON docs USING gin (tsv);</span><br />
<br />
I also added the aforementioned trigger to maintain the tsvector. I got slightly fancy with this, indexing both the title and the body fields, and giving them different weights for ranking purposes:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">CREATE FUNCTION update_tsv() RETURNS trigger</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> LANGUAGE 'plpgsql' VOLATILE NOT LEAKPROOF</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">AS $BODY$</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">begin</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> new.tsv :=</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> setweight(to_tsvector('pg_catalog.english',</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> coalesce(new.title,'')), 'A') ||</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> setweight(to_tsvector('pg_catalog.english',</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> coalesce(new.body,'')), 'D');</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> return new;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">end</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">$BODY$;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;">CREATE TRIGGER update_tsv</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> BEFORE INSERT OR UPDATE ON docs</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> FOR EACH ROW EXECUTE PROCEDURE update_tsv();</span><br />
<br />
Now that we have somewhere to store our data, here's the code to load it with data. This should be run in a Python Virtual Environment that has the <i>psycopg2</i> and <i>BeautifulSoup4</i> packages installed:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">from bs4 import BeautifulSoup</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">import psycopg2</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">import glob</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"># Load from the docs/ directory</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">path = 'docs/*.html'</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"># Connect to the database and begin a transaction</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">conn = psycopg2.connect("dbname=fts_blog user=fts_blog password=fts_blog")</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">cur = conn.cursor()</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;">for file in glob.iglob(path):</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> print("Loading: {} ".format(file))</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"> with open(file) as f:</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> html = f.read()</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"> # Read the HTML and extract the title and body</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> soup = BeautifulSoup(html, features="html.parser")</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> title = soup.find('title').decode_contents(formatter="html").replace('&nbsp;', ' ')</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> body = soup.find('body').decode_contents(formatter="html")</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"> # Create the SQL statement</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sql = """INSERT INTO docs</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> (path, title, body)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> VALUES</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> (%s, %s, %s)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> ON CONFLICT ON CONSTRAINT docs_pkey DO UPDATE SET</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> title = %s,</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> body = %s;"""</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"> cur.execute(sql, [file, title, body, title, body])</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;">conn.commit()</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">cur.close()</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">conn.close()</span><br />
<br />
As you can see, the code is pretty simple. It basically just loops around all the HTML files found in the docs/ subdirectory, uses BeautifulSoup4 to extract the document title and body, and then runs an INSERT on the database table. For convenience, the query also handles conflicts to allow easy re-indexing.<br />
<br />
To load some data, I created a directory calls docs/ alongside the script, and copied the PostgreSQL HTML documentation into the directory. I could then run the indexer:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">$ python indexer.py</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">Loading: docs/sql-alterroutine.html </span><br />
<span style="font-family: "courier new" , "courier" , monospace;">Loading: docs/backup-dump.html </span><br />
<span style="font-family: "courier new" , "courier" , monospace;">Loading: docs/gist-examples.html </span><br />
<span style="font-family: "courier new" , "courier" , monospace;">...</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">...</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;">Process finished with exit code 0</span><br />
<br />
Finally, I needed to test the search capabilities. That's done through a SQL query of course, for example;<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">SELECT</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> ts_rank("tsv", plainto_tsquery('trigger')) AS "rank",</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> path,</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> title,</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> ts_headline(body,</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> plainto_tsquery('trigger'),</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> 'StartSel=*,StopSel=*,MaxFragments=2,' ||</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> 'FragmentDelimiter=...,MaxWords=30,MinWords=1') AS "headline"</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">FROM</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> docs</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">WHERE</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> tsv @@ plainto_tsquery('trigger')</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">ORDER BY rank DESC LIMIT 20</span><br />
<br />
So here we are selecting the document rank, the path and title of the document, as well as a headline - a small snippet of the document that highlights where the matches were found within it, as you'd probably see on your favourite search engine. That's all coming from the docs table of course, and is restricted by our search query and then sorted by the rank and limited to 20 results.<br />
<br />
NOTE: The search term in the query above is 'trigger'. This word is actually included three times in the query text, so make sure you change them all if using the query above as a starting point for your own.<br />
<br />
When we run the query we see the output below which can of course, be used however we like in an application.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihm0zoAUVNMgwUgd4Y8-lzn1DF_ABUN568T7n1vVR2OHiNzjY8fQDz8p17eOQq_IQsBK2p7XcgxCFr9s1Ys6xgifP4z0cZad7FX_jOLGzsu1ANIy5fKC9kE9WGWOVLVQoVt_0tYKPQCRu7/s1600/Screenshot+2019-06-30+at+10.44.29.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1067" data-original-width="1600" height="425" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihm0zoAUVNMgwUgd4Y8-lzn1DF_ABUN568T7n1vVR2OHiNzjY8fQDz8p17eOQq_IQsBK2p7XcgxCFr9s1Ys6xgifP4z0cZad7FX_jOLGzsu1ANIy5fKC9kE9WGWOVLVQoVt_0tYKPQCRu7/s640/Screenshot+2019-06-30+at+10.44.29.png" width="640" /></a></div>
<br />
So there we have it; a table with an index and trigger, and a trivial Python script and we have a working indexer and search engine. Oh, and in case you were wondering, no, this is not new functionality in PostgreSQL. It was added to the core server with version 8.3 which was released in 2008. Prior to that there was a contrib module called tsearch2, offering largely equivalent functionality from version 7.4 which was released in 2003. Do try to keep up Mongo 😀</div>
Dave Pagehttp://www.blogger.com/profile/10523190286514017933noreply@blogger.com3tag:blogger.com,1999:blog-3645070705954691807.post-76387430226891267772019-05-29T22:11:00.002+01:002019-05-29T22:12:19.014+01:00Avoiding Gmail's confidential mode<div dir="ltr" style="text-align: left;" trbidi="on">
So this is one of the very few (maybe the first?) blog entries I've written that aren't directly related to <a href="https://www.postgresql.org/">PostgreSQL</a>, however, it does affect how I (and others) may work on the project.<br />
<br />
Last night I received email from Google about my personal G Suite account which I use for all my day-to-day email, which for the most part is related to work on pgAdmin and PostgreSQL. Google were proudly announcing the rollout of their new Gmail Confidential Mode update. If you've not come across this yet, then essentially what it does is allow users to send emails that can be deleted or expired after a certain amount of time, optionally require SMS verification to open them, and prevent printing (but not screen-shots of course), forwarding or downloading etc.<br />
<br />
When using the Gmail web interface, this all works fairly seamlessly. I can see why some people would want it if that's all they use, however, like many people, I also use other clients, for example, via IMAP. In that case, instead of the original email Gmail sends a placeholder email to replace the actual message which contains a link to allow you to login to Google and view the message online (assuming the SMS verification passes and the message hasn't been deleted or expired of course). That's going to be quite inconvenient to me, besides which, I really don't want anyone to be able to control access to emails they've sent me, after I've received them.<br />
<br />
There's another problem affecting PostgreSQL's mailing lists however. How long will it be until someone sends such a message to one of the PostgreSQL lists, where it will do nothing but clutter up the archives and annoy other users (who won't be able to read the message anyway as they won't be able to login to Google as pgsql-hackers@postgresql.org or whatever the list address was)?<br />
<h3 style="text-align: left;">
<br /></h3>
<h3 style="text-align: left;">
Fixing the PostgreSQL mail servers</h3>
<div style="text-align: left;">
After some discussion with some of the PostgreSQL sysadmin team, we discovered that Gmail adds a header to the messages that have confidential mode enabled (X-Gm-Locker: <token>). This is easy for us to deal with - Stefan was very quickly able to add a rule to the Exim configuration on our inbound SMTP servers that will immediately reject any messages with this header, sending a message back to the user explaining why their message was rejected, and to resend it with confidential mode disabled.</div>
<h3 style="text-align: left;">
<br /></h3>
<h3 style="text-align: left;">
Fixing my Gmail outbound email</h3>
<div>
Disabling confidential mode for my own outbound email was pretty easy:</div>
<div>
<ol style="text-align: left;">
<li>Select the option to "Manage this domain"</li>
<li>Navigate the G Suite admin interface to <i>Apps</i> -> <i>G Suite</i> -> <i>Gmail</i> -> <i>User Settings</i>.</li>
<li>Under<i> Confidential Mode</i>, set the <i>Disable</i> option, and save the settings.</li>
</ol>
<div>
Now, confidential mode will no longer be available in the compose message interface in Gmail. Well, once the settings have propagated around and the UI has refreshed anyway.</div>
</div>
<h3 style="text-align: left;">
<br /></h3>
<h3 style="text-align: left;">
Rejecting confidential messages from Gmail</h3>
<div>
This part is a little more complex, and <b>requires a suitable G Suite account</b>. It will not work with plain Gmail.</div>
<div>
<ul style="text-align: left;">
<li>Select the option to "Manage this domain"</li>
<li>Navigate the G Suite admin interface to <i>Apps</i> -> <i>G Suite</i> -> <i>Gmail</i> -> <i>Advanced Settings -> Content compliance</i></li>
<li>Add a policy:</li>
<ul>
<li>Give it a name</li>
<li>Select "<i>Inbound</i>" for the email messages to be affected in section 1.</li>
<li>Set the option for "<i>If ANY of the following match the message</i>" in section 2, then add a new expression:</li>
<ul>
<li>Set the type to "<i>Metadata match</i>".</li>
<li>Select "<i>Gmail confidential mode</i>" under Attribute.</li>
<li>Select "<i>Message is in Gmail confidential mode</i>" under Match type.</li>
<li>Save the expression.</li>
</ul>
</ul>
</ul>
<ul style="text-align: left;">
<li>In section 3, select <i>Reject the message</i> as the action and enter a suitable custom message, e.g. "<i>This domain does not accept messages sent with Gmail Confidential Mode enabled. Please disable and resend.</i>"</li>
<li>Save the settings.</li>
</ul>
<div>
You should now find that email is rejected by Google, if confidential mode is enabled. I had my friend Jonathan send me a test message, which was duly rejected with the message:</div>
<div>
<br /></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">550 5.7.1 This domain does not accept messages sent with Gmail Confidential Mode enabled. Please disable and resend. - gcdp z20sor188854uan.2 - gsmtp</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><br />There. Normality has been restored.</span></div>
<div>
<br /></div>
</div>
</div>
Dave Pagehttp://www.blogger.com/profile/10523190286514017933noreply@blogger.comtag:blogger.com,1999:blog-3645070705954691807.post-42105530888144276252018-09-05T12:04:00.002+01:002020-05-27T13:25:10.658+01:00Why do we install as root?<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="caret-color: rgb(0, 0, 0); font-family: HelveticaNeue; font-size: 12px; text-size-adjust: auto;">
<span style="font-size: 14px;">A couple of common questions I hear from customers (usually long-time users of a particular database from Redwood) via our guys in the field is “<i>why do we install our software as<span class="Apple-converted-space"> </span>root?</i>” And “<i>why do we run services as<span class="Apple-converted-space"> </span>postgres?</i>”. The simple, TLDR; answer is “<i>for security</i>”. For a detailed explanation, read on…</span></div>
<div style="caret-color: rgb(0, 0, 0); font-family: HelveticaNeue; font-size: 12px; text-size-adjust: auto;">
<span style="font-size: 14px;"><br /></span></div>
<div style="caret-color: rgb(0, 0, 0); font-family: HelveticaNeue; font-size: 12px; text-size-adjust: auto;">
<span style="font-size: 14px;">A basic principle when securing a software installation is “<i>install with maximum privilege requirements and run with minimal</i>”. In practice this equates to having software being installed and binaries/executables etc. owned by the<span class="Apple-converted-space"> </span><i>root</i><span class="Apple-converted-space"> </span>user, whilst the services themselves are actually run under a minimally privileged (and ideally dedicated) service user account, typically<span class="Apple-converted-space"> </span><i>postgres</i><span class="Apple-converted-space"> </span>in a PostgreSQL installation. Data files, and any other files that need to be modified by the software in normal operation are also owned by the service user account.</span></div>
<div style="caret-color: rgb(0, 0, 0); font-family: HelveticaNeue; font-size: 12px; text-size-adjust: auto;">
<span style="font-size: 14px;"><br /></span></div>
<div style="caret-color: rgb(0, 0, 0); font-family: HelveticaNeue; font-size: 12px; text-size-adjust: auto;">
<span style="font-size: 14px;">Let’s look at the running software first. Postgres (which will in fact refuse to run as<span class="Apple-converted-space"> </span><i>root</i>), is a server process which is often running on a network port that is accessible from other nodes on the network. Of course, we should limit access as much as possible to only those nodes that need access using both a firewall (even simple <a href="https://linux.die.net/man/8/iptables">iptables</a> rules will work),<span class="Apple-converted-space"> </span><b>and</b><span class="Apple-converted-space"> </span>Postgres’ <a href="https://www.postgresql.org/docs/10/static/auth-pg-hba-conf.html">pg_hba.conf</a> access control file, but even with those measures in place, it’s possible that a determined attacker (let’s call him Zero Cool) can still gain access to the port the database server is running on.</span></div>
<div style="caret-color: rgb(0, 0, 0); font-family: HelveticaNeue; font-size: 12px; text-size-adjust: auto;">
<span style="font-size: 14px;"><br /></span></div>
<div style="caret-color: rgb(0, 0, 0); font-family: HelveticaNeue; font-size: 12px; text-size-adjust: auto;">
<span style="font-size: 14px;">Once our arch-nemesis Zero Cool has access to the database server port, he needs a way to escalate his attack. This may involve exploiting an unknown security issue in Postgres itself (as with any software, we hope there are none but we’re kidding ourselves if we think it’s totally secure), or it may be that he’s used other techniques such as social engineering to learn a users credentials.</span></div>
<div style="caret-color: rgb(0, 0, 0); font-family: HelveticaNeue; font-size: 12px; text-size-adjust: auto;">
<span style="font-size: 14px;"><br /></span></div>
<div style="caret-color: rgb(0, 0, 0); font-family: HelveticaNeue; font-size: 12px; text-size-adjust: auto;">
<span style="font-size: 14px;">If Zero gains “regular” access to Postgres, then he will be subject to any security measures (access control lists, RLS policies etc) that limit the scope of what the user account he’s used can access/delete/update/whatever. If the user account has superuser privileges or access to un-trusted procedural languages, or if Zero gained access using a lower-level exploit that allows him to execute arbitrary code in other ways, then he will be able to wreak chaos at a lower level in the system, such as overwriting files on disk.</span></div>
<div style="caret-color: rgb(0, 0, 0); font-family: HelveticaNeue; font-size: 12px; text-size-adjust: auto;">
<span style="font-size: 14px;"><br /></span></div>
<div style="caret-color: rgb(0, 0, 0); font-family: HelveticaNeue; font-size: 12px; text-size-adjust: auto;">
<span style="font-size: 14px;">However - and this is the important bit - assuming there are no exploits in the Operating System that he can leverage to gain further privileges, his chaos will be restricted to things that the service account under which Postgres is running can do. In a well secured system where an unprivileged account like p<i>ostgres</i><span class="Apple-converted-space"> </span>is used, that will be limited to damage to the Postgres data files and other files (or processes etc) that user can modify or control. If Postgres were running under a privileged account like<span class="Apple-converted-space"> </span><i>root</i>, Zero would have pwned (in script-kiddie parlance) the entire system at this point!</span></div>
<div style="caret-color: rgb(0, 0, 0); font-family: HelveticaNeue; font-size: 12px; text-size-adjust: auto;">
<span style="font-size: 14px;"><br /></span></div>
<div style="caret-color: rgb(0, 0, 0); font-family: HelveticaNeue; font-size: 12px; text-size-adjust: auto;">
<span style="font-size: 14px;">Now consider the case where the Postgres software files were also owned by the<span class="Apple-converted-space"> </span><i>postgres</i><span class="Apple-converted-space"> </span>user. Zero would not only be able to affect files and processes owned by the service account, but would also be able to modify the software itself, allowing him the opportunity to add backdoors for future access or other malware such as spyware etc. In the case of software that is started as<span class="Apple-converted-space"> </span><i>root</i><span class="Apple-converted-space"> </span>(even that which later drops those privileges or switches to another user account for normal operation), this could be exploited to gain even easier privileged access at a later time.</span></div>
<div style="caret-color: rgb(0, 0, 0); font-family: HelveticaNeue; font-size: 12px; text-size-adjust: auto;">
<span style="font-size: 14px;"><br /></span></div>
<div style="caret-color: rgb(0, 0, 0); font-family: HelveticaNeue; font-size: 12px; text-size-adjust: auto;">
<span style="font-size: 14px;">This is why we want our software to be installed and owned by a high privilege user such as<span class="Apple-converted-space"> </span><i>root</i> and run as a low privileged user such as<i> postgres</i>. Doing so ensures that even if Zero manages to crack his way into Postgres and potentially access or modify data, he cannot modify the software or other aspects of the host system and thus has a much harder time further escalating his attack.</span></div>
</div>
Dave Pagehttp://www.blogger.com/profile/10523190286514017933noreply@blogger.com2tag:blogger.com,1999:blog-3645070705954691807.post-70525995273535073462017-07-13T16:45:00.000+01:002017-07-13T16:49:57.424+01:00Some numbers, oh, and pgAdmin 4 v1.6 was released<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="tr_bq">
<span style="font-family: inherit;">It's been far too long since I wrote a blog post (again!), so in a bid to start fixing that, here's an easy one:</span></div>
<blockquote style="text-align: left;">
<h2 style="text-align: left;">
<span style="font-family: inherit;">pgAdmin 4 v1.6 released</span></h2>
<span style="font-family: inherit;">The pgAdmin Development Team are pleased to announce the release of <a href="https://www.pgadmin.org/">pgAdmin 4</a> version 1.6. This release of pgAdmin 4 includes over 70 bug fixes and a dozen new features. For details, please see the <a href="https://www.pgadmin.org/docs/pgadmin4/dev/release_notes_1_6.html">release notes</a>.<br />Notable changes in this release include:</span><br />
<ul style="text-align: left;">
<li><span style="font-family: inherit;">Significant performance improvements on Windows, massively reducing initial load time and improving UI response for the vast majority of users during testing.</span></li>
</ul>
<ul style="text-align: left;">
<li><span style="font-family: inherit;">Enhancements to the Query Tool enabling the viewing of large query resultsets far more quickly. For example, a simple test query with 96K rows rendered results within 1 second vs. 22 seconds in pgAdmin III during testing!</span></li>
</ul>
<ul style="text-align: left;">
<li><span style="font-family: inherit;">A major rewrite of the Query History tab allows browsing of queries executed in the query tool with full details including the entire query, in a much nicer user interface.</span></li>
</ul>
<ul style="text-align: left;">
<li><span style="font-family: inherit;">The Desktop Runtime now supports detachable tabs, allowing the Query Tool and Debugger to be opened in new tabs and then moved to alternate displays (from 1.5 this was possible in web mode only)</span></li>
</ul>
<ul style="text-align: left;">
<li><span style="font-family: inherit;">The Query Tool's Results Grid has been overhauled with a new, sleek look an feel supporting selection of arbitrary rows, columns or blocks of cells with full copy support and column sizing retention.</span></li>
</ul>
<ul style="text-align: left;">
<li><span style="font-family: inherit;">The Dashboard tab can now be closed if desired, to minimise query traffic resulting from graph updates.</span></li>
</ul>
<span style="font-family: inherit;">For more information, checkout the <a href="https://www.pgadmin.org/docs/pgadmin4/dev/index.html">online documentation</a>, the <a href="https://www.pgadmin.org/screenshots">screenshots</a>, and of course the <a href="https://www.pgadmin.org/download">download page</a>.</span></blockquote>
<h2 style="text-align: left;">
<span style="font-family: inherit;">Collaboration</span></h2>
<span style="font-family: inherit;">For those that aren't aware, pgAdmin 4 is the fourth complete rewrite of pgAdmin since the first code was written way back in 1998(!). One of the major aims of the technology change we made for the new version was to help attract new developers as we always found them extremely hard to find when we used C++ (we now use Python and Javascript). To kickstart the project, <a href="http://www.enterprisedb.com/">EnterpriseDB</a> (my employer) allowed me to make use of one of our development teams and we embarked on a pretty epic journey to get the first release done. Much of that is documented in an <a href="http://pgsnake.blogspot.co.uk/2016/04/pgadmin-4-elephant-nears-finish-line.html">earlier post</a>.</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">We were happy to find that our plan worked - better than expected in fact - when <a href="https://pivotal.io/">Pivotal</a> started contributing heavily to the project, bringing new skills and techniques that have enabled us to improve both pgAdmin and the way we design and develop it even further. This release of pgAdmin is the first to include major changes in functionality developed by the Pivotal team, which you can see in the overhauled Query Tool result grid UI and the enhanced Query History tab.</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">Today, community members, EnterpriseDB staff and Pivotal staff all collaborate to make pgAdmin better and better. Look out on the mailing lists for ways you can get involved, by joining periodic meetings we hold, participating in user focus sessions, or contributing to the project in any other way that may interest you.</span><br />
<h2 style="text-align: left;">
<span style="font-family: inherit;">Numbers</span></h2>
<div>
<span style="font-family: inherit;">Well, I promised you some numbers and here they are. This info was compiled for my boss Marc last week who was curious to see how things were working out expanding the developer base on the project. Numbers are based on 2017-01-01 - 2017-07-05 stats from the GIT repository:</span></div>
<div>
<span style="font-family: inherit;"><br /></span></div>
<div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">Number of committed changes by EDB: 256 (mostly smaller items such as bug fixes)</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">Number of committed changes by Pivotal: 67 (some larger changes)</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">Number of committed changes by Others: 62</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">Lines of code added by EDB: 32,542</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">Lines of code added by Pivotal: 110,017</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">Lines of code added by others: 19,221</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">Lines of code removed by EDB: 20,056</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">Lines of code removed by Pivotal: 35,688</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">Lines of code removed by Others: 2,184</span></div>
<div>
<span style="font-family: inherit;"><br /></span></div>
</div>
<div>
<span style="font-family: inherit;">I love it when a plan comes together :-)</span></div>
<span style="font-family: inherit;"><br /></span>
</div>
Dave Pagehttp://www.blogger.com/profile/10523190286514017933noreply@blogger.com0tag:blogger.com,1999:blog-3645070705954691807.post-75159068654177634542016-04-15T10:25:00.003+01:002016-05-24T10:05:49.446+01:00pgAdmin 4 - The elephant nears the finish line<div dir="ltr" style="text-align: left;" trbidi="on">
As you may know, many of us from the <a href="http://www.pgadmin.org/">pgAdmin</a> team have been hard at work on pgAdmin 4 for some time now. pgAdmin 4 is a complete rewrite of pgAdmin (the fourth, as you may guess), the previous version having reached the end of it's maintainable life after 14 years of development.<br />
<br />
Work on the project began slowly, almost two years ago, however the team at <a href="http://www.enterprisedb.com/">EnterpriseDB</a> have ramped up the development pace over the last few months. Right now, we're approaching alpha-readiness which we expect to be at within a few weeks.<br />
<h2 style="text-align: left;">
</h2>
<h2 style="text-align: left;">
Architecture</h2>
This new application is designed for operation on both the desktop and a webserver. Written in Python using the <a href="http://flask.pocoo.org/">Flask</a> framework for the backend, and Javascript/<a href="https://jquery.com/">jQuery</a>/<a href="http://backbonejs.org/">Backbone</a> for the frontend, it can easily be deployed as a WSGI application for multiple users in practically any network environment. A small runtime application allows it to be run as a desktop application - this is a Qt executable that incorporates a Python interpreter and web browser along with the main application in a single package that can be installed on a developer laptop as with previous versions of pgAdmin.<br />
<h2 style="text-align: left;">
</h2>
<h2 style="text-align: left;">
Functionality</h2>
Whilst the core functionality of pgAdmin 4 remains similar to pgAdmin 3, there are a number of changes we've made:<br />
<br />
<ul style="text-align: left;">
<li>Support for unsupported database versions has been dropped.</li>
<li>We haven't re-implemented support for some object types that no one really used in the tool before - for example, operator classes and families.</li>
<li>We haven't (yet) reimplemented some of the tools that didn't work so well in pgAdmin 3, such as the graphical query builder or database designer (which was always disabled entirely by default).</li>
<li>The Query Tool and Edit Grid have been merged into a single tool. Over coming releases we'll be improving the functionality further to allow in-grid updates to be made to results from arbitrary queries (where a query is determined to be updateable). For now though, updating is allowed when pgAdmin knows the data source is a single table with a primary key.</li>
<li>The user interface is more flexible than ever, allowing tabs to be docked and re-arranged in more ways than previously.</li>
<li>We've spent time redesigning some of the UI paradigms in pgAdmin 3. Gone are the list controls with Add/Remove buttons, replaced with what we call sub-node grid controls that will allow in-grid editing of key values, with more detail available when needed in expandable rows.</li>
<li>We also spend time thinking about how to make it faster to use pgAdmin, by minimising the need to switch between dialogues, using searchable combo boxes and more.</li>
<li>The UI is much more attractive, making use of control groupings and expandable regions to make things more readable.</li>
</ul>
<div>
<h2 style="text-align: left;">
</h2>
<h2 style="text-align: left;">
Screenshots</h2>
So, enough of the babble, here are some pre-release, semi-polished screenshots:<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNZ7k7QqwpZxgZlGBcoD7E13Pb2VxTN7P7NUwdEdXYfiMmh1ggazwEyS7EG4dasnkg6SgKlhEyVOFycMeUYHv8MGn6ROJVdxdgE1ktAGrynKWRrlWkGQsLBrb1_9XDucQ19SL2tePnH-y3/s1600/Screen+Shot+2016-04-15+at+09.39.42.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="502" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNZ7k7QqwpZxgZlGBcoD7E13Pb2VxTN7P7NUwdEdXYfiMmh1ggazwEyS7EG4dasnkg6SgKlhEyVOFycMeUYHv8MGn6ROJVdxdgE1ktAGrynKWRrlWkGQsLBrb1_9XDucQ19SL2tePnH-y3/s640/Screen+Shot+2016-04-15+at+09.39.42.png" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><div style="font-size: 12.8px;">
<span style="text-align: left;">The main user interface, showing the properties of a function. </span></div>
<div>
<span style="text-align: left;"><br /></span></div>
</td></tr>
</tbody></table>
</div>
<div>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidZRb-3wJHtIiIJnSWTDJzF1lpKFQM0SfetuFDzNcTtluNdQjPugkF0hUcrGHyOK-k9BhoQLy3-04Uk5KZJqG8AQYvkVPluEcnq2nbuOM53avz0m-nO626QGc9E8OkIc_SjaboO4Kh5q1B/s1600/Screen+Shot+2016-04-15+at+09.24.49.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="402" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidZRb-3wJHtIiIJnSWTDJzF1lpKFQM0SfetuFDzNcTtluNdQjPugkF0hUcrGHyOK-k9BhoQLy3-04Uk5KZJqG8AQYvkVPluEcnq2nbuOM53avz0m-nO626QGc9E8OkIc_SjaboO4Kh5q1B/s640/Screen+Shot+2016-04-15+at+09.24.49.png" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><span style="text-align: left;">Setting the ACL on a function. </span></td></tr>
</tbody></table>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh34pASJ5wyfRp3pH4IVPEv4Uk8xKuLbYMERs78Gel6E0BM5xIuQv2Kjpu0bU31MXt05JDvwfJyBo62f8jAWE6xslIugSH10G14oVGNZGheBgBbyYLyKcG4ZXgLkpFABR_6ZzP_tHh2AB9b/s1600/Screen+Shot+2016-04-15+at+09.38.36.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="404" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh34pASJ5wyfRp3pH4IVPEv4Uk8xKuLbYMERs78Gel6E0BM5xIuQv2Kjpu0bU31MXt05JDvwfJyBo62f8jAWE6xslIugSH10G14oVGNZGheBgBbyYLyKcG4ZXgLkpFABR_6ZzP_tHh2AB9b/s640/Screen+Shot+2016-04-15+at+09.38.36.png" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><span style="text-align: left;">Adding a member to a composite type using the sub-node grid control. </span></td></tr>
</tbody></table>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEit5R_XX6mu49HRU3zWiH-CczaGSnYIZM-b8yIm3irsLKUk75BpHeLDgGqIfKzrEhciTv1M4XdP-93KLjDbWQhLvTubjnI_UYO8wz4KR-7byep_F2E_yIvKCzV-wp_WU92CJ9r_3qVSpffj/s1600/Screen+Shot+2016-04-15+at+09.29.16.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="502" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEit5R_XX6mu49HRU3zWiH-CczaGSnYIZM-b8yIm3irsLKUk75BpHeLDgGqIfKzrEhciTv1M4XdP-93KLjDbWQhLvTubjnI_UYO8wz4KR-7byep_F2E_yIvKCzV-wp_WU92CJ9r_3qVSpffj/s640/Screen+Shot+2016-04-15+at+09.29.16.png" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">The Query Tool and Data Editor.</td></tr>
</tbody></table>
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgelO5STm2Ex1It27AAHhgM27TYNQyqplyroltoWr9KfJ9h3THodHG9VyJZ0lJC9WG8A32cnwfAhffyPM66d0XeoEC2bG34yS9W_YUX4UsnvUIo0oN-TkgJYNFD8SpgYpb8OcDPenJJDcBw/s1600/Screen+Shot+2016-04-15+at+09.27.08.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="502" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgelO5STm2Ex1It27AAHhgM27TYNQyqplyroltoWr9KfJ9h3THodHG9VyJZ0lJC9WG8A32cnwfAhffyPM66d0XeoEC2bG34yS9W_YUX4UsnvUIo0oN-TkgJYNFD8SpgYpb8OcDPenJJDcBw/s640/Screen+Shot+2016-04-15+at+09.27.08.png" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">The Procedural Language Debugger.</td></tr>
</tbody></table>
<h2 style="text-align: left;">
Team</h2>
<span id="goog_1622235113"></span><span id="goog_1622235114"></span>As you can imagine, there has been a significant amount of work done to get to this stage, and I really need to express my gratitude to those who have contributed, as well as the executive management team at EnterpriseDB who have allowed me to commit so many people to this project:<br />
<br />
<div style="text-align: left;">
<b>Project leadership</b></div>
<ul style="text-align: left;">
<li>Ashesh Vashi (engineering team manager, code guru)</li>
<li>Karen Blatchley (project manager)</li>
</ul>
<h3 style="text-align: left;">
</h3>
<div style="text-align: left;">
<b>Development team</b></div>
<div>
<ul style="text-align: left;">
<li>Khushboo Vashi</li>
<li>Akshay Joshi</li>
<li>Arun Kollan</li>
<li>Harshal Dhumal</li>
<li>Murtuza Zabuawala</li>
<li>Neel Patel</li>
<li>Sanket Mehta</li>
<li>Surinder Kumar</li>
</ul>
<h3 style="text-align: left;">
</h3>
<div style="text-align: left;">
<b>Packaging</b></div>
</div>
<div>
<ul style="text-align: left;">
<li>Muhammad Aqeel</li>
<li>Paresh More</li>
<li>Sandeep Thakkar</li>
</ul>
<div>
<br /></div>
<div style="text-align: left;">
<b>QA</b></div>
</div>
<div>
<ul style="text-align: left;">
<li>Priyanka Shendge</li>
<li>Fahar Abbas</li>
</ul>
<div>
<br /></div>
<div>
Of course, there are also community members who are starting to contribute fixes and other improvements, such as Thom Brown (on his own time, not EDBs), Seçkin Alan, Ronan Dunklau and Prasad Somwanshi, all of whom (along with others I may have missed) deserve thanks.</div>
<h2 style="text-align: left;">
</h2>
<h2 style="text-align: left;">
Want to help or learn more?</h2>
<div>
If you want to help, you can <a href="http://git.postgresql.org/gitweb/?p=pgadmin4.git;a=summary">checkout the code</a> and start playing with it. We're not yet feature complete (for example, the Tables node in the treeview is still in development), but we're pretty close. Feel free to try out the code, and report or better yet, fix any bugs or issues you may find. If you wish to start working on new features that is also welcome, but please do email the <a href="mailto:pgadmin-hackers@postgresql.org">hackers list</a> first to ensure your work is not something that's already on our project plan!</div>
<div>
<br /></div>
<div>
If anyone would like to talk more about pgAdmin 4, I'll be at <a href="http://www.pgconf.us/2016/">PGConf.US</a> next week - the organisers know me well and should be able to help you find me for a chat or demo. See you there!</div>
<div>
<br /></div>
</div>
</div>
</div>
Dave Pagehttp://www.blogger.com/profile/10523190286514017933noreply@blogger.com19tag:blogger.com,1999:blog-3645070705954691807.post-84789388221718609922014-12-07T12:30:00.000+00:002014-12-10T05:03:12.284+00:00The story of pgAdmin<div dir="ltr" style="text-align: left;" trbidi="on">
<div>
<i>I’m often asked how I first became involved in <a href="http://www.postgresql.org/">PostgreSQL</a>, and how the <a href="http://www.pgadmin.org/">pgAdmin</a> project got started. Much as I’m happy to tell the story over beer, it becomes tedious to do so over email after the first half-dozen or so times. So in a vain attempt to save my fingers from future carpal tunnel syndrome, here’s the tale…</i></div>
<div>
<br /></div>
<div>
I first discovered PostgreSQL in 1997. I was working at a social housing provider in the UK where, at the time, we were running Informix SE and Pick databases. We wanted to start building in-house web applications for managing additional areas of the business. There were no useful interfaces for Pick that could be used with any of the web technologies available to us at the time and the licensing model for Informix and its ODBC driver was prohibitively expensive, so I started looking for alternatives. </div>
<div>
<br /></div>
<div>
After spending some time researching mSQL and MySQL, I eventually found PostgreSQL, which seemed to offer everything we needed—a SQL-based DBMS with ODBC and C interfaces, a liberal license, a rich set of features, and a vibrant community supporting it that was responsive to bug reports and open to feature requests. My colleagues and I developed a number of applications using PostgreSQL in the following years and the database became a critical part of our business. We had applications written in PHP for the web as well as Visual Basic for the desktop users. </div>
<div>
<br /></div>
<div>
However, it was early on in our journey with Postgres that, as a Windows shop (on the desktop at least), we found ourselves wishing for a good graphical management tool for the database. At the time, the only option was pgAccess, which was a basic tool written in TCL/TK that was not easy to get to work on Windows. So I decided to write my own in Visual Basic. The first version was never released to the public and was modeled on the pgAccess design. Called pgManager, it was quickly abandoned as we found the UI to be restrictive and, well, not overly usable. The second incarnation was pgAdmin, which was redesigned from the ground up. It was released as an Open Source project and quickly became a popular tool amongst Postgres users (which was extremely gratifying as it was my first Open Source project). </div>
<div>
<br /></div>
<div>
Some years later, we once again found ourselves suffering due to the way the application had been designed. I shut myself away for a couple of weeks whilst my family were away visiting relatives in Scotland and wrote pgAdmin II, using a far more structured and maintainable design that implemented a much more usable user interface. I was particularly proud of the design and cleanliness of that code, but by 2002, we needed to start over again. This time it wasn't the application design that was holding us back, but rather the choice of technology. Visual Basic didn't handle internationalization or localization well, nor did it run on platforms other than Windows without the use of WINE, under which it was never particularly stable. The hard decision was made to rewrite everything again, this time using C++ with the wxWidgets framework. pgAdmin III looked much like pgAdmin II, but it solved all of the problems our (in particular) Japanese Linux-using friends were experiencing. Now in its third incarnation, pgAdmin remains the most popular Open Source GUI tool for managing Postgres.</div>
<div>
<br /></div>
<div>
I continued to work at the housing provider and make good use of Postgres until 2007, at which time I moved to <a href="http://www.enterprisedb.com/">EnterpriseDB</a> so I could concentrate on my Postgres work full time. At EDB, not only do I work on our own tools, I also continue to contribute to the PostgreSQL community in various ways. I have the privilege of having full support of the management at EDB for my community work, allowing me the freedom to work on whatever I need to in order to fulfill my community roles on the core team, the <a href="http://www.postgresql.eu/">PostgreSQL Europe</a> board, and of course, pgAdmin. One of the products I’m responsible for at EDB is <a href="http://www.enterprisedb.com/products-services-training/products/postgres-enterprise-manager">Postgres Enterprise Manager</a>, which has a fat client interface that’s based on pgAdmin. This has allowed us to put more effort into the maintenance of pgAdmin, with members of the PEM team at EDB regularly contributing code, fixing bugs and reviewing the patches from elsewhere. Ashesh Vashi has even been made a committer for his efforts.</div>
<div>
<br /></div>
<div>
Despite the hard work over the last 12 or so years, the pgAdmin developers have come to the conclusion that there is limited life left in the current code. Aside from it being extremely hard to find C++ developers these days, we’ve also run into numerous difficult-to-fix bugs that can be traced back to issues or design choices in our code, the underlying wxWidgets framework, and some versions of GTK (the choice of which, we typically have no control as we inherit GTK from the users’ Linux distribution). What’s more, the world is shifting to a web based model these days. Cloud deployments are becoming more widely used, as well as simple instances supplied through hosting providers. Users are getting used to being able to pickup their laptop or a tablet and do whatever they need to do without having to install software – open a browser, read and write email, build a spreadsheet or create a database!</div>
<div>
<br /></div>
<div>
Consequently, we're now starting to design pgAdmin 4. The plan is to build a single Python-based pgAdmin that users can either deploy on a web server or run from their desktop. It’ll be designed to “live query” databases rather than query and cache schema details like the current implementation (which was designed with use over a dialup connection in mind!), and we’ll be making the application much more task focused (and therefore usable) than it is at present, as well as implementing some of the oft-requested features that are hard to do in the current code such as direct data editing in the query tool and tabbed query windows. The new code will also be extensible from the ground up, allowing support for new object types or functionality to be simply “dropped in” and auto-discovered at runtime.</div>
<div>
<br /></div>
<div>
We're still in the proof of concept stage at the moment though, so there's nothing available to download just yet. But stay tuned as work kicks up a gear over the next 12 months!</div>
<div>
<br /></div>
</div>
Dave Pagehttp://www.blogger.com/profile/10523190286514017933noreply@blogger.com16tag:blogger.com,1999:blog-3645070705954691807.post-39060505569679588942012-09-28T16:54:00.002+01:002012-09-28T16:54:42.632+01:00PostgreSQL Conference Europe 2012 - 3 weeks to go!<div dir="ltr" style="text-align: left;" trbidi="on">
There are less than four weeks until this years PostgreSQL Conference Europe, to be held at the Corinthia Hotel, Prague, organised by PostgreSQL Europe. This years event is shaping up to be our best ever, with a higher rate of attendee registrations than we've ever seen before, a bumper crop of talks from PostgreSQL users and contributors from around the world, and a keynote presentation from well known industry veteran, renowned author on all things SQL, and one of the original authors of the SQL89 and SQL92 standards, Joe Celko.<br />
<br />
For more information on the event, visit the <a href="http://2012.pgconf.eu/">conference website</a>.<br />
<br />
This years conference will start with a day of training sessions (available at extra cost), places on which must be pre-booked when you <a href="http://2012.pgconf.eu/registration/">register</a>:<br />
<ul style="text-align: left;">
<li>Joe Celko - <i>A day of SQL with Celko</i>: How often does an opportunity to learn SQL from the master come along? Not very often! Not to be missed if you're new to SQL or want to polish up your skills.<br /></li>
<li>Bruce Momjian, Devrim Gunduz (EnterpriseDB) - <i>Mastering PostgreSQL Administration</i>: Hone your mastery of PostgreSQL, learning from Bruce, one of the project founders, and long term contributor and RPM maintainer, Devrim. This day long session starts with the basics, moving into more advanced administration and management topics.<br /></li>
<li>Greg Smith, Peter Geoghegan (2nd Quadrant) - <i>PostgreSQL Performance Training</i>: Renowned PostgreSQL performance expert Greg and developer Peter spend half a day showing you how to get the best performance from your database servers.<br /></li>
<li>Dimitri Fontaine, Simon Riggs (2nd Quadrant) - <i>PostgreSQL Replication Training</i>: PostgreSQL developer Dimitri and committer and author of features such as hot standby and point in time recovery, Simon, spend half a day discussing the streaming and synchronous replication, as well as failover and monitoring options available to PostgreSQL users.<br /></li>
<li>Pavel Stehule - <span style="color: black; font-family: inherit; text-decoration: none;"><a href="http://www.postgresql.eu/events/schedule/pgconfeu2012/session/285-implementace-ulozenych-procedur-v-postgresql/" style="color: black; font-style: italic; text-decoration: none;">Implementace uložených procedur v PostgreSQL</a>: Veteran contributor to PostgreSQL's stored functions languages Pavel shows you how to write stored procedures in PostgreSQL. Half day, presented in Czech.<br /></span></li>
<li><span style="color: black; font-family: inherit; text-decoration: none;">Tomas Vondra (GoodData) -<span style="font-family: inherit;"><i> </i></span></span><span style="color: black; font-family: inherit; text-decoration: none;"><a href="http://www.postgresql.eu/events/schedule/pgconfeu2012/session/286-cteni-exekucnich-planu/" style="color: black; font-style: italic; text-decoration: none;">Čtení exekučních plánů</a>: Long time contributor Tomas teaches the finer points of reading query (EXPLAIN) plans. Half day, presented in Czech.</span></li>
</ul>
<div>
Following the training sessions, we have three days of talks in three different rooms, with something for everyone - check out the <a href="http://www.postgresql.eu/events/schedule/pgconfeu2012/">conference schedule</a> for details - and as always there will be social events hosted by our sponsors, and the hallway track to meet and chat with other PostgreSQL users and developers.</div>
<div>
<br /></div>
<div>
Convinced? Of course you are. Head over to the <a href="http://2012.pgconf.eu/registration/">registration page</a> and book your place now!</div>
</div>
Dave Pagehttp://www.blogger.com/profile/10523190286514017933noreply@blogger.com0tag:blogger.com,1999:blog-3645070705954691807.post-879926468812135872012-03-26T15:54:00.002+01:002012-03-27T10:44:16.970+01:00PostgreSQL Developer Meeting 2012<div dir="ltr" style="text-align: left;" trbidi="on">
For the last few years I've organised a by-invitation meeting of the most active developers in the PostgreSQL community the day before <a href="http://www.pgcon.org/">PGCon</a>'s sessions start in Ottawa. This is undoubtedly the best time of the year to do this as the vast majority of PostgreSQL hackers attend this event - many only attend local events for the rest of the year.<br />
<br />
As you can imagine, figuring out how to keep the meeting productive is not an easy task. Opening it up to anyone to attend is not really an option unfortunately, as we would be unlikely to be able to provide a suitable conference room for large numbers of people due to the cost of the space (which really needs to be comfortable as we'll be sitting around a table for nine hours or so) and the food. Budget aside; having too many people in attendance makes it very difficult to have a productive meeting, a problem we believe we had last year when we had our highest number of attendees to date (around 30) and what many felt was our least productive meeting to date.<br />
<br />
This year, the Core Team spent quite some time discussing the upcoming meeting and came to the conclusion that we needed to do two things to make the meeting a success again:<br />
<br />
1) We need to spend more time fine-tuning the agenda.<br />
<br />
We try to leave the agenda up to the attendees - after all, it is their meeting. However, there is a tendency to include items that haven't really been thought through in enough detail in advance to make good discussion points. This year we're working to ensure the agenda contains only items that have had some amount of thought given to them already, but are yet to be resolved. Ideally, we suspect many of these topics will be things that have been discussed on the mailing lists already, but haven't come to any sort of conclusion. That happens from time to time, and face to face discussion often helps reach consensus on the way forward. What we don't want to discuss are the ideas that we all have after a couple of drinks at the pub, scribbled on the back of a beer mat, added to the agenda and then forgotten about. Those ideas are best saved for the evenings of the conference (with more beer)!<br />
<br />
2) We need to reduce the number of attendees at the meeting.<br />
<br />
This was the <b>really</b> hard one. We decided that we should reduce the number of attendees down from last years 30 to maybe 20 - 25. Two of those people are Josh Berkus and myself who are primarily there to take notes and chair the meeting respectively. The rest of the attendees come from a variety of backgrounds typically - committers, active developers, future (hopefully) active developers, past developers and senior staff from Postgres-friendly companies. This year we decided that the criteria for invitations would be "has this person been a significant contributor to PostgreSQL 9.2"? We've applied that test to all the proposed attendees, and whilst many people who received invitations are amongst the normal crowd, unfortunately it does mean that some people who have attended in the past were not sent invitations this year. If you're one of those people, please accept my apologies - hopefully this blog post will clarify why that is the case.<br />
<br />
Of course, it is possible we have overlooked inviting people who have made significant contributions to the upcoming 9.2 release. If you're one of those people, please let me know if you would like to attend.<br />
<br />
Regardless of whether or not you'll be at the developer meeting dear readers, I do hope to see you in Ottawa in May.</div>Dave Pagehttp://www.blogger.com/profile/10523190286514017933noreply@blogger.com0tag:blogger.com,1999:blog-3645070705954691807.post-81300075065719226262011-12-21T15:01:00.002+00:002011-12-21T15:04:58.448+00:00Updated PostgreSQL Download Infrastructure<div dir="ltr" style="text-align: left;" trbidi="on">
Back at the tail end of November, the PostgreSQL sysadmin/web team <a href="http://blog.hagander.net/archives/202-www.postgresql.org-brand-new,-yet-old-and-familiar.html">successfully migrated</a> the PostgreSQL website to a <a href="http://www.postgresql.org/about/news/1365/">new platform</a>, based on PostgreSQL, Django, Lighttpd, and Varnish, to replace the old somewhat complex and messy PHP platform used in the past. Functionally and visually the website is almost identical to what it was, though the behind-the-scenes management interface is now vastly improved, as is the "Your Account" section which now offers users much more control over their submissions like news and events etc.<br />
<br />
One other change that went largely unnoticed however was in the downloads section of the website, specifically the <a href="http://www.postgresql.org/ftp/">FTP area</a>. This is a web interface over the content on the PostgreSQL FTP site, ftp.postgresql.org, that gives users a nice way to browse the files and directories on the site. On the old website, when the user clicked to download a file they would then be taken to a page of flags where they could select a mirror site to download the file from. The mirrors were all third party servers over which we had no control, aside from an automated system to ensure we only listed those which had content which was no more than 48 hours out of date. This arrangement made sense years ago when bandwidth was more precious, however with the bandwidth available to us these days it's really just clunky and inconvenient for users to have to choose one of 75 flags to reach a server that may not be entirely up to date.<br />
<br />
To resolve this, as the new website infrastructure went live we also pushed a new download infrastructure into production. What you'll see now is direct links to files on download servers we run ourselves, from the website. This gives us a number of advantages:<br />
<br />
<ul style="text-align: left;">
<li>The user has a much slicker experience when downloading, both in terms of the workflow, and often the speed of downloads (because some of the old mirrors were much faster than others).</li>
<li>We can push out files to the download servers in minutes, rather than days.</li>
<li>We can collect meaningful statistics to help us understand what users are downloading.</li>
<li>We can automatically (and invisibly) disable download servers in the event of problems, within minutes.</li>
</ul>
<div>
So what does this mean to the end user? Well, for a user coming from the website downloads take fewer clicks and avoid the "flags" page which could be daunting and potentially confusing. For those using the FTP site directly, there are a number of changes:</div>
<div>
<ul style="text-align: left;">
<li>The site can be accessed at <a href="ftp://ftp.postgresql.org/">ftp://ftp.postgresql.org/</a>, rather than using one of the individual mirror hostnames we used in the past.</li>
<li>Because there are no third party mirrors, there are no inconsistent paths to the content (one mirror in the past may have had content under /pub/mirrors/postgresql/ whilst another may have used /u/postgres/).</li>
<li>We can now offer downloads over HTTP using the same paths as FTP - you can use <a href="http://ftp.postgresql.org/pub/...">http://ftp.postgresql.org/pub/...</a> or <a href="ftp://ftp.postgresql.org/pub/...">ftp://ftp.postgresql.org/pub/...</a></li>
<li>We've also opened up RSYNC access to all users, where previously only registered mirror sites could rsync the downloads from us: <a href="rsync://ftp.postgresql.org::pgsql-ftp">rsync://ftp.postgresql.org::pgsql-ftp</a>.</li>
</ul>
<div>
Because there are lots of links to files on the old mirror network on the web, we've left the old mirror hostnames in place for the time being (though they are no longer being monitored), however they will be phased out over time.</div>
</div>
<div>
<br /></div>
<div>
Happy downloading!</div>
</div>Dave Pagehttp://www.blogger.com/profile/10523190286514017933noreply@blogger.com0tag:blogger.com,1999:blog-3645070705954691807.post-24950529628195632872011-10-11T15:38:00.000+01:002011-10-11T15:39:09.853+01:00To upgrade or not to upgrade? That is the question.<div dir="ltr" style="text-align: left;" trbidi="on">
On a fairly regular basis the Postgres community hear from users who are complaining of bugs in old versions of Postgres - they'll post a bug report or a request for help on one of the mailing lists saying something along the lines of having run into an issue with PostgreSQL 8.4.2 for example, and immediately be met with suggestions to upgrade to the latest version because there have been 37 bug fixes and 5 security issues resolved since 8.4.2 was released.<br />
<br />
Generally this happens with one of two types of users. There are those that just don't bother to upgrade (who we can do little about), and those that don't upgrade because they are concerned that changes to PostgreSQL will break their application. This latter class of user is sometimes also restricted by what they can do by corporate policies in their workplace.<br />
<br />
The Postgres developers are mindful of this issue and have practices in place to allow users to safely upgrade without significant risk of behavioural changes breaking their applications. The practice is really quite simple; minor upgrades of PostgreSQL <b>never</b> include new features.<br />
<br />
What does this mean in practice? Well, PostgreSQL version numbers are in 3 parts, X.Y.Z (some packages such as the EnterpriseDB installers also add a build suffix):<br />
<br />
<b>X.Y</b>: This is the <b>major</b> version number of the server, for example, 8.4, 9.0 or 9.1. New major versions may include new functionality, require upgrades to the database files on disk and generally require <b>thorough</b> testing of applications prior to deployment.<br />
<br />
<b>Z</b>: This is the <b>minor</b> version number. This number is increased for bug fixes releases, also known as "point releases". These releases never include new features; only carefully applied bug fixes. Point releases are fully compatible with previous point releases of the same major version and should require minimal testing prior to deployment on existing installations.<br />
<br />
Returning to our opening example, the user in this case is not being told to upgrade to 9.1.1 - the latest and greatest release at the time of writing, complete with a myriad of new features and changes from 8.4.2 - but to 8.4.9, the latest point release in the 8.4 series, which is functionally identical to 8.4.2. This numbering scheme and the processes behind it are specifically designed to allow users to safely and easily upgrade their database servers to minimise the number of known bugs in the software; in fact the <b>PostgreSQL developers consider not upgrading </b><b>to the latest point release </b><b>to be riskier than upgrading</b>.<br />
<br />
So next time you're "certifying" your application with PostgreSQL, aim to certify it with a specific major version of the server, and avoid getting into a situation where you prevent yourself from updating to the latest point release as doing so can cause more problems than it can solve, and if you're a sysadmin or DBA rest assured that point releases won't introduce functionality changes and should be welcomed and installed as soon as possible!<br />
<br /></div>Dave Pagehttp://www.blogger.com/profile/10523190286514017933noreply@blogger.com0tag:blogger.com,1999:blog-3645070705954691807.post-32262296972785378792011-10-05T17:31:00.002+01:002011-10-05T17:36:40.495+01:00StackBuilder Package Updates<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
If you've ever used one of the PostgreSQL installers for v8.2 or above, either the old Windows MSI installer or the newer "one click" installers that also support Linux and Mac, you'll probably have come across StackBuilder. For those that haven't or those that never found the time, StackBuilder was introduced with the PostgreSQL 8.2 installer to allow us to distribute different components of PostgreSQL independently of the server itself. Originally in 8.0/8.1, the installer included lots of additional products, such as the ODBC, JDBC, OLEDB and .NET drivers, Slony and PostGIS. As you can imagine, this proved near impossible to maintain as we needed to try to coordinate the release of products from multiple independently run projects.<br />
<br />
StackBuilder was the solution to this. The installers were cut down to essentially include just the PostgreSQL server, pgAdmin and StackBuilder, which allowed us to provide all the other components on independent release schedules. It also gave us a vehicle to encourage adoption of PostgreSQL by other Open Source projects, by including packages for them as well, so with a few mouse clicks a user could be up and running with a "stack" like Drupal, Apache, PHP and PostgreSQL on their OS of choice. We made a conscious decision to include software other than well known Open Source products in the StackBuilder catalog as well, with the aim of giving the user as much choice of product to use with PostgreSQL as possible - as a result we have products in the catalog from multiple vendors and projects, as well as the "PostgreSQL family" projects. If you're interested in having a PostgreSQL related product included in the catalog, please contact me to discuss how we can make that happen.<br />
<br />
So, with the refresher course out of the way, todays blog post is prompted by the latest round of updates that I've been adding to the catalog - we've got 42 updates to the Open Source packages, as well as 18 new packages, including for the first time, pgBouncer, pgMemcache and Drupal 7.<br />
<br />
<b>Updates:</b><br />
<br />
<ul style="text-align: left;">
<li>Apache/PHP 2.2.20-5.3.8: Linux32, Linux64, Mac, Win32</li>
<li>mediaWiki 1.17.0: Linux32, Linux64, Mac, Win32</li>
<li>pgJDBC 9.0-801: Linux32, Linux64, Mac, Win32</li>
<li>phpBB 9.0-801: Linux32, Linux64, Mac, Win32</li>
<li>PostGIS 1.3.6 for PG 8.3: Linux32, Linux64, Mac</li>
<li>PostGIS 1.4.2 for PG 8.4: Linux32, Linux64, Mac</li>
<li>PostGIS 1.5.3 for PG 9.0: Linux32, Linux64, Mac</li>
<li>psqlODBC 09.00.0310: Linux32, Linux64, Mac, Win32</li>
<li>Slony 1.2.22 for PG 8.3: Linux32, Linux64, Mac</li>
<li>Slony 2.0.7 for PG 8.4: Linux32, Linux64, Mac</li>
<li>Slony 2.0.7 for PG 9.0: Linux32, Linux64, Mac</li>
<li>Npgsql 2.0.11: Linux32, Linux64, Mac, Win32</li>
</ul>
<br />
<b>New releases:</b><br />
<br />
<ul>
<li>Drupal 7: Linux32, Linux64, Mac, Win32</li>
<li>pgBouncer: Linux32, Linux64</li>
<li>pgMemcache 2.0.1 for PG 9.0: Linux32, Linux64, Mac</li>
<li>pgMemcache 2.0.1 for PG 9.1: Linux32, Linux64, Mac</li>
<li>PostGIS 1.5.3 for PG 9.1: Linux32, Linux64, Mac</li>
<li>Slony 2.0.7 for PG 9.1: Linux32, Linux64, Mac</li>
</ul>
<br />
Note that the PostGIS community maintain the PostGIS installers for Windows which will be released as soon as they're available. Windows updates for Slony are still in development due to an issue found in QA. Mac and Windows builds of pgBouncer are on their way.<br />
<br />
To download and install any of these packages, just run StackBuilder - if you don't have it already you can get it with the <a href="http://www.enterprisedb.com/products-services-training/pgdownload">PostgreSQL Installers</a>.<br />
<br />
Enjoy :-)<br />
<div>
<br /></div>
</div>
Dave Pagehttp://www.blogger.com/profile/10523190286514017933noreply@blogger.com5tag:blogger.com,1999:blog-3645070705954691807.post-79295357773559557642011-10-04T09:11:00.003+01:002011-10-04T10:05:10.974+01:00PostgreSQL Conference Europe: Are you ready?PostgreSQL Conference Europe 2011 starts 2 weeks from today in the beautiful city of Amsterdam in the Netherlands. This is the fourth annual conference hosted by PostgreSQL Europe, following on from extremely successful events in Prato (Italy), Paris and Stuttgart, and is aimed at developers, DBAs, technologists and decision makers either using, or considering using the world's most advanced Open Source database.<div><br /></div><div>This year we have four days on the schedule, with a kick-off day of training sessions hosted by respected PostgreSQL developers such as Greg Smith, Bruce Momjian, Magnus Hagander, Guillaume Lelarge and <a href="http://www.postgresql.eu/events/sessions/pgconfeu2011/">more</a>. Topics will cover performance tuning, application development, database administration, replication & high availability and geospatial. The training sessions are available on their own, or as part of a regular conference attendance at additional - but very reasonable - cost.</div><div><br /></div><div>We had a record number of talk proposals submitted this year but we've resisted the urge to host even more sessions in parallel - in fact we've reduced the number of parallel sessions to three as we all know how frustrating it can be when more than one that you want to see are at the same time. Instead we've extended the conference by a day to accomodate over 40 different sessions, which has the added bonus of allowing an additional night of social activities - always a great way to discuss the latest technologies, trends and ideas with other Postgres users over a beer or two.</div><div><br /></div><div>We've got a great range of topics for this year, covering new features in PostgreSQL 9.1 and beyond, developing applications, running Postgres in the cloud, hacking PostgreSQL internals, tools and add-on products and managing large databases, presented by a wide cross-section of users and developers, including a number of this year's <a href="http://code.google.com/soc/">Google Summer of Code</a> students who will talk about their work. You can view the <a href="http://www.postgresql.eu/events/schedule/pgconfeu2011/">complete schedule</a> on the conference website.</div><div><br /></div><div>Our opening keynote this year will be presented by <a href="http://www.afilias.info/biographies/ram-mohan">Ram Mohan</a>, EVP and CTO of <a href="http://www.afilias.info/">Afilias</a> who manage the .info, .org and .mobi top level domains using Postgres. Ram will be discussing the business decisions and strategy around their use of PostgreSQL. Our closing keynote will be presented by <a href="http://enterprisedb.com/company/executive-team">Ed Boyajian</a>, President and CEO of <a href="http://www.enterprisedb.com/">EnterpriseDB</a> who will discuss PostgreSQL's role in the post-Oracle era.</div><div><br /></div><div>So, if you haven't done so already, head on over to the website to <a href="http://2011.pgconf.eu/registration/">register as an attendee</a> to avoid missing out on what promises to be an outstanding conference in an fantastic location. See you in Amsterdam!</div>Dave Pagehttp://www.blogger.com/profile/10523190286514017933noreply@blogger.com2tag:blogger.com,1999:blog-3645070705954691807.post-32641560086763329932011-08-01T16:57:00.007+01:002011-08-01T17:43:17.962+01:00PostgreSQL 9.1, meet MySQL<span class="Apple-style-span">So having spent the last few months with my head buried deep in a <a href="http://www.enterprisedb.com/products-services-training/products/postgres-enterprise-manager">project</a> at work, I finally managed to get back to my <a href="http://pgsnake.blogspot.com/2011/04/tinkering-with-sqlmed.html">previous</a> hacking on SQL/MED at the weekend after the kids went away for a week (good $DEITY it's quiet here)! Within a couple of hours, I had my half-baked Foreign Data Wrapper for MySQL up and running, and am now able to create foreign table objects in PostgreSQL 9.1 that map either directly to tables in a MySQL database, or to queries run on the MySQL server.</span><div><span class="Apple-style-span"><br /></span></div><div><span class="Apple-style-span">Here's an example:</span></div><br /><span class="Apple-style-span" ><span class="Apple-style-span" >-- Create the required functions for the FDW.<br />CREATE FUNCTION mysql_fdw_handler()<br /> RETURNS fdw_handler<br /> AS '$libdir/mysql_fdw'<br /> LANGUAGE C STRICT;<br /><br />CREATE FUNCTION mysql_fdw_validator(text[], oid)<br /> RETURNS void<br /> AS '$libdir/mysql_fdw'<br /> LANGUAGE C STRICT;<br /><br />-- Create the data wrapper or "transport".<br />CREATE FOREIGN DATA WRAPPER mysql_fdw<br /> HANDLER mysql_fdw_handler<br /> VALIDATOR mysql_fdw_validator;<br /><br />-- Create the foreign server, a pointer to the MySQL server.<br />CREATE SERVER mysql_svr<br /> FOREIGN DATA WRAPPER mysql_fdw<br /> OPTIONS (address '127.0.0.1', port '3306');<br /><br />-- Create one or more foreign tables on the MySQL server. The first of<br />-- these maps to a remote table, whilst the second uses an SQL query.<br />CREATE FOREIGN TABLE employees (<br /> id integer,<br /> name text,<br /> address text)<br /> SERVER mysql_svr<br /> OPTIONS (table 'hr.employees');<br /><br />CREATE FOREIGN TABLE ex_staff (<br /> id integer,<br /> name text,<br /> address text)<br /> SERVER mysql_svr<br /> OPTIONS (query 'SELECT * FROM hr.employees WHERE date_left IS NOT NULL');</span><br /><br /></span><div><div><span class="Apple-style-span" >-- Create a user mapping to tell the FDW the username/password to </span></div><div><span class="Apple-style-span" >-- use to connect to MySQL, for PUBLIC. This could be done on a per-</span></div><div><span class="Apple-style-span" >-- role basis.</span></div><div><span class="Apple-style-span" >CREATE USER MAPPING FOR PUBLIC </span></div><div><span class="Apple-style-span" > SERVER mysql </span></div><div><span class="Apple-style-span" > OPTIONS (username 'dpage', password '');</span></div><div><br /></div><div><span class="Apple-style-span">So let's try it out. Here's the test table in MySQL:</span></div><div><span class="Apple-style-span"><br /></span></div><div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; ">mysql> SELECT * FROM employees;</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; ">+----+-------------+----------------------------+------------+</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; ">| id | name | address | date_left |</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; ">+----+-------------+----------------------------+------------+</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; ">| 1 | Dave Page | 27 High Street, Georgetown | NULL |</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; ">| 2 | Fred Bloggs | 46 Mill Road, Klasborough | NULL |</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; ">| 3 | Fred Bloggs | 24 The Wharf, Westhampton | 2010-05-23 |</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; ">+----+-------------+----------------------------+------------+</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; ">3 rows in set (0.00 sec)</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; "><br /></div><div><span class="Apple-style-span">And here we are in PostgreSQL 9.1 beta 3:</span></div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; "><br /></div><div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; ">postgres=# SELECT * FROM employees;</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; "> id | name | address </div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; ">----+-------------+----------------------------</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; "> 1 | Dave Page | 27 High Street, Georgetown</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; "> 2 | Fred Bloggs | 46 Mill Road, Klasborough</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; "> 3 | Fred Bloggs | 24 The Wharf, Westhampton</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; ">(3 rows)</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; "><br /></div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; ">postgres=# SELECT * FROM ex_staff;</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; "> id | name | address </div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; ">----+-------------+---------------------------</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; "> 3 | Fred Bloggs | 24 The Wharf, Westhampton</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; ">(1 row)</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; "><br /></div><div><span class="Apple-style-span">For the curious, here's what the EXPLAIN output looks like:</span></div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; "><br /></div><div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; ">postgres=# EXPLAIN SELECT * FROM employees;</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; "> QUERY PLAN </div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; ">----------------------------------------------------------------</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; "> Foreign Scan on employees (cost=10.00..13.00 rows=3 width=68)</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; "> Local server startup cost: 10</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; "> MySQL query: SELECT * FROM hr.employees</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; ">(3 rows)</div><div style="color: rgb(153, 0, 0); font-family: 'courier new'; "><br /></div><div><span class="Apple-style-span">Pretty neat huh? There are a couple of limitations in the current implementation:</span></div></div></div></div><div><ul><li><span class="Apple-style-span">No attempt is currently made to push down quals (WHERE clauses) to the MySQL server, so every row MySQL finds is returned to PostgreSQL and filtered there. There's no defined API for this in PostgreSQL yet, and it's not immediately clear how to build something more complex than the simple example I used in my <a href="https://github.com/dpage/redis_fdw">Redis FDW</a> that would be required for a remote relational database. That said, you can build WHERE clauses into the foreign table definition of course.</span></li><li><span class="Apple-style-span">The MySQL C API doesn't seem to offer a simple way to either randomly access a result set, or at least reset the cursor to the first row, unless you copy the entire resultset to the client (PostgreSQL in this case). Because we need to be able to return to the first row if PostgreSQL calls the Rescan function, we therefore currently copy the entire resultset, rather than reading it from the server, on demand.</span></li></ul><div><span class="Apple-style-span">Aside from minor tweaks, this is probably about as far as I'll take this little project for now. I'll be talking about it at both <a href="http://postgresopen.org/2011/home/">Postgres Open 2011</a> in Chicago in September, and <a href="http://pgbr.postgresql.org.br/2011/evento.php">PGBR 2011</a> i<span class="Apple-style-span">n </span></span><span class="Apple-style-span" style="font-family: georgia; font-size: medium; ">São Paulo in November - hopefully I'll see you there.</span></div></div><div><span class="Apple-style-span" style="font-family: georgia; font-size: medium; "><br /></span></div><div><span class="Apple-style-span" style="font-family: georgia; font-size: medium; ">The MySQL FDW source code is <a href="https://github.com/dpage/mysql_fdw">available on Github</a>, and uses the PostgreSQL licence.</span></div></div>Dave Pagehttp://www.blogger.com/profile/10523190286514017933noreply@blogger.com2tag:blogger.com,1999:blog-3645070705954691807.post-76897915739855091652011-04-06T17:14:00.000+01:002011-04-06T17:14:09.547+01:00Google Summer of Code 2011<span class="Apple-style-span" style="border-collapse: collapse; color: #222222; font-family: arial, sans-serif; font-size: 13px;">As a reminder, Google's <a href="http://code.google.com/soc/">Summer of Code 2011</a> program has started and PostgreSQL is participating! We are looking for students who are interested in hacking on pgAdmin or PostgreSQL all summer. You only have until April 8, 2011 at midnight Pacific time to submit a proposal.<br />
<br />
If you are a student, or know a student, or are a professor, please urge your students to participate in Summer Of Code. Not only is it a fantastic opportunity to learn how open source code is really made, and get paid to do it, but it can also be career-building.<br />
<br />
Read up on the PostgreSQL GSOC <a href="http://wiki.postgresql.org/wiki/GSoC_2011">wiki page</a>.<br />
</span>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3645070705954691807.post-67144373501014073862011-04-01T15:45:00.002+01:002011-04-02T08:09:48.576+01:00Tinkering with SQL/MEDOne of the cool new features in the upcoming PostgreSQL 9.1 release is support for SQL/MED<span class="Apple-style-span" style="font-family: inherit;">, or <span class="Apple-style-span" style="line-height: 19px;"><i>Management of External Data</i>. Essentially what this allows us to do is connect external data sources to PostgreSQL and query them as if they were tables. The initial support in 9.1 will offer read-only support for <i>foreign tables, </i>with the ability to include qualifier pushdown - i.e, the ability to push the applicable WHERE part of the SQL query to the remote server, to minimise data transfer. This optimisation is dependent on each individual <i><a href="http://developer.postgresql.org/pgdocs/postgres/sql-alterforeigndatawrapper.html">Foreign Data Wrapper</a> (FDW)</i> - the driver used to connect to the remote data source - which may or may not implement it. </span></span><br />
<span class="Apple-style-span" style="font-family: inherit;"><span class="Apple-style-span" style="line-height: 19px;"><br />
</span></span><br />
<span class="Apple-style-span" style="font-family: inherit;"><span class="Apple-style-span" style="line-height: 19px;">PostgreSQL 9.1 will ship with an FDW that allows <a href="http://developer.postgresql.org/pgdocs/postgres/file-fdw.html">access to files</a> in formats that can be read by the existing COPY command, and Andrew Dunstan has been working on an FDW that will allow access to files with <a href="http://people.planetpostgresql.org/andrew/index.php?/archives/167-And-the-new-FDW-is-....html">fixed-width data</a> as well as one for accessing <a href="http://people.planetpostgresql.org/andrew/index.php?/archives/158-Yay!.html">ragged CSV files</a>.</span></span><br />
<span class="Apple-style-span" style="font-family: inherit;"><span class="Apple-style-span" style="line-height: 19px;"><br />
</span></span><br />
<span class="Apple-style-span" style="font-family: inherit;"><span class="Apple-style-span" style="line-height: 19px;">I've been a big proponent of SQL/MED support, particularly at work, so was pleased to finally find some time to look at the work that's been done for 9.1 earlier in the week. I don't have much interest in reading external files (though clearly that is an important feature for some users) - I'm far more keen to see PostgreSQL have the ability to access the data in all those other databases you may not be able to migrate away from, or are better suited to particular workloads. So, with that in mind, I picked a NoSQL database more or less at random (though intentionally one that's a simple key/value store) and started hacking on an FDW for <a href="http://redis.io/">Redis</a>.</span></span><br />
<span class="Apple-style-span" style="font-family: inherit;"><span class="Apple-style-span" style="line-height: 19px;"><br />
</span></span><br />
<span class="Apple-style-span" style="line-height: 19px;"><a href="https://github.com/dpage/redis_fdw">The code</a> is still pre-alpha quality, and there are a number of TODOs, including:</span><br />
<ul><li><span class="Apple-style-span" style="line-height: 19px;">Add qual pushdown</span></li>
<li><span class="Apple-style-span" style="line-height: 19px;">Handle different Redis datatypes correctly</span></li>
<li><span class="Apple-style-span" style="line-height: 19px;">Figure out how to make Redis scans atomic (or at the least</span><span class="Apple-style-span" style="line-height: 19px;"> properly handle tuples that have been removed since the initial</span><span class="Apple-style-span" style="line-height: 19px;"> scan)</span></li>
<li><span class="Apple-style-span" style="line-height: 19px;">Handle Redis authentication</span></li>
</ul><span class="Apple-style-span" style="line-height: 19px;">However it seems to work well (bar the known issues of course), and is looking like it will be simple to use as the basis for more complex FDWs which may well keep me amused for a while longer.</span><br />
<span class="Apple-style-span" style="line-height: 19px;"><br />
</span><br />
<span class="Apple-style-span" style="line-height: 19px;">As always with Open Source, feel free to play with this if you think you might find it useful, but don't blame me if it breaks (which it likely will)!</span>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3645070705954691807.post-6955773966478587012011-03-24T15:45:00.002+00:002011-03-24T15:46:33.268+00:00Debunking the FUD: PostgreSQL for Microsoft Windows Payload ExecutionIf you follow Twitter for keywords like "postgres" or "postgresql", you may well have seen a number of tweets over the last day or so regarding a so-called "Payload Execution" exploit in PostgreSQL. This supposed attack was apparently first described in this <a href="http://sqlmap.sourceforge.net/doc/BlackHat-Europe-09-Damele-A-G-Advanced-SQL-injection-whitepaper.pdf">paper</a>, and has hit Twitter after <a href="http://www.metasploit.com/redmine/projects/framework/repository/revisions/12111/entry/modules/exploits/windows/postgres/postgres_payload.rb">code</a> to demonstrate the issue was added to <a href="http://www.metasploit.com/">Metasploit</a>.<br />
<br />
The "attack" works like this:<br />
<ol><li>A user uploads a payload as a large object to the database. This can be done using the client side <a href="http://www.postgresql.org/docs/current/interactive/lo-interfaces.html">lo_create()</a> and <a href="http://www.postgresql.org/docs/current/interactive/lo-interfaces.html">lo_import()</a> functions.<br />
</li>
<li>The user then uses the server-side <a href="http://www.postgresql.org/docs/current/interactive/lo-funcs.html">lo_export()</a> function to export the payload to the server's filesystem.<br />
</li>
<li>The user then executes the payload, by creating an SQL level function to wrap the C function in the payload, eg:<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">CREATE FUNCTION do_bad_stuff() RETURNS int4 AS '$libdir/bad_stuff.dll' LANGUAGE C;<br />
</span></li>
<li><span class="Apple-style-span" style="font-family: inherit;">The user then executes the payload:</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SELECT do_bad_stuff();</span></li>
</ol><span class="Apple-style-span" style="font-family: inherit;">Here's the problem with the "exploit":</span><br />
<div><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></div><div><b><i>You need to be a superuser for it to work.</i></b></div><div><b><i><br />
</i></b></div><div>That's right - steps 2 and 3 above both require that you are a superuser - in other words, you already have complete, unfettered access to the database server.</div><div><br />
</div><div>But wait - that doesn't get you very far into the OS either! One oft-criticized feature of PostgreSQL (for users that unwisely like to do all their work as root) is that it refuses to run under an account with superuser privileges - in fact on Windows, we irrevocably remove unwanted privileges from our security token at startup. The reason for this, is to ensure that once our L337 H4X0r has uploaded his payload (using his existing superuser privileges), he cannot escalate those privileges to the operating system's superuser accounts and compromise other parts of the system.</div><div><br />
</div><div>As a side note - as superuser in most installations there are far easier ways of achieving the results above that avoid the need to know the architecture of the system or to write code in C - for example, you can simply create a function using one of the untrusted procedural languages, like pl/perlu. </div><div><br />
</div><div>So the bottom line is, <b><i>this is not an exploit</i></b> - it requires that you are already a database superuser with all the power that entails, and doesn't gain you anything you didn't already have the ability to do, or give you a way to gain OS level privileges beyond those already held by the low-privilege user account the server runs under.</div><div><br />
</div><div><br />
</div>Unknownnoreply@blogger.com3tag:blogger.com,1999:blog-3645070705954691807.post-59062202917550023232010-11-24T11:38:00.000+00:002010-11-24T11:38:44.237+00:00Just 2 days left to register for PGDay.EU 2010Yup, you heard correctly - there are just two (and a bit) days left to <a href="http://2010.pgday.eu/register">register</a> for the annual European PostgreSQL Conference, <a href="http://2010.pgday.eu/">pgDay.EU 2010</a>, being held in Stuttgart on December 6th and 7th, with a day of training sessions on the 8th.<br />
<br />
With over <a href="https://www.postgresql.eu/events/schedule/pgday2010/">40 talks</a> in a mix of English and German, this is an event not to be missed if you're a PostgreSQL user, developer, hobbyist, or are considering a deployment. There are a wide range of topics including talks on GIS, interoperability and migration, high availability and monitoring, business around PostgreSQL and case studies, as well as more academic topics.<br />
<br />
On day three we have a number of training courses available, including a two part course on PostGIS, presented by one of the leading developers, Mark Cave-Ayland from <a href="http://www.siriusit.co.uk/">Sirius</a>, deployment of applications in the Cloud with Servoy presented by Robert Ivens from <a href="http://www.roclasi.com/">Roclasi</a>, and a two part PostgreSQL administration course (in German) given by Andreas Scherbaum for <a href="http://www.enterprisedb.com/">EnterpriseDB</a>.<br />
<br />
Finally, as attendees from previous PGDay's will have come to expect, <a href="http://www.enterprisedb.com/">EnterpriseDB</a> will be hosting a party for everyone on Monday night - definitely not one to miss!<br />
<br />
So, talk to the boss, fill out those pesky travel requisition forms, and head on over to the <a href="http://2010.pgday.eu/register">registration page</a>!Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3645070705954691807.post-49669284072230455992010-11-06T05:54:00.000+00:002010-11-06T05:54:52.958+00:00PGWest: Day 3PGWest; the final day. It started (for me at least) with Greg Smith giving another excellent talk on scaling PostgreSQL with Hot Standby. This was followed by Robert Hodges and Linas Virbalas talking about replication of data from MySQL to PostgreSQL using <a href="http://www.continuent.com/">Continuent Tungsten</a>.<br />
<br />
The highlight/lowlight of the day - depending on your viewpoint - was Rob Wultsch's talk on MySQL. Rob primarily looked at some of the things MySQL does better than PostgreSQL, and also talked about the forks (or lack of them, if you discount patchsets - which I personally, do not) and the FUD. Now lets be honest here - Rob did make some perfectly valid points about MySQL; for example, the fact that it's replication is easy to setup. Now to take this example, I would argue that PostgreSQL isn't that hard to get going either - Robert Haas' <a href="http://www.linux.com/learn/tutorials/377071-using-replication-in-postgresql-90">tutorial</a> illustrates that nicely - but MySQL is arguably better <i>at the moment</i>. For most of the points he raised, there are easy counter-arguments that can be made by PostgreSQL, as shown by JD who made a number of us cringe a little with an impromptu rebuttal session afterwards.<br />
<br />
In my view, this whole session was doomed to failure. It's fine to point out some of the things that PostgreSQL can learn from MySQL, but the session as given glossed over everything that PostgreSQL can do but MySQL cannot - which, to someone unfamiliar with PostgreSQL could give an incorrect impression. For the end user who is selecting a database, it is important not to choose a product based on whether some features are better implemented in one database or another, but to choose based on the quality of the products, the reliability of them, and the availability of the features you <i>actually need</i>.<br />
<br />
On reflection, I think the only way we can tackle this sort of comparison fairly in a talk session, is to have a proper, moderated debate between a PostgreSQL expert, and a MySQL expect. Who knows, maybe we can do that for PG East or PG Europe 2011.<br />
<br />
Regardless of that - kudos to Rob for having the bottle to stand up and talk about MySQL in front of a room of PostgreSQL users. It was never going to be an easy crowd.<br />
<br />
After lunch, I did a little work for a while, and then toddled off to Rob Treat's presentation on <i>PostgreSQL 9.0: The other stuff</i>. We joked the night before that no-one knew what <i>The stuff</i> was, never mind <i>The other stuff</i>, but I guess once he knuckled down to his slides, he realised it meant Hot Standby and Streaming Replication. The talk was pretty good in the end, though it did remind me a little of <a href="http://wiki.postgresql.org/wiki/Image:Developments_in_PostgreSQL_9.0.pdf">my talk</a> in Brussels earlier this year, which could have been entitled <i>PostgreSQL 9.0: The stuff, and the other stuff</i>!<br />
<br />
And that's where the conference ended for me, as we took the opportunity to hold an <a href="http://www.enterprisedb.com/">EnterpriseDB</a> meeting whilst a we were in the same city. All in all, an excellent show, with a great turnout - and to top it off, The Register took notice of us with two <a href="http://www.theregister.co.uk/2010/11/04/mcnealy_ellions_opensource/">different</a> <a href="http://www.theregister.co.uk/2010/11/05/enterprisedb_postgresql_redhat/">stories</a> - which somehow makes it feel all worthwhile.Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-3645070705954691807.post-70425475885715394012010-11-04T16:42:00.000+00:002010-11-04T16:42:01.911+00:00Booked for PGDay.EU 2010 yet?If you're planning on attending this year's European PostgreSQL conference (increasingly inaccurately known as PGDay!), then you might want to think about registering and booking your travel and accommodation now. It's just over a month until the conference, and isn't uncommon for the price of flights and trains to start to rise as the date gets nearer.<br />
<br />
We have <a href="http://2010.pgday.eu/schedule">42 sessions this year</a>, with a wide variety of PostgreSQL talks in English and German, followed by a day of training sessions, covering PostgreSQL Administration, PostGIS, Hot Standby and using Servoy with PostgreSQL. Places on the training sessions are limited, and available on a first come, first served basis at a (relatively small) extra cost.<br />
<br />
We have two guest keynote speakers: Simon Phipps is giving the opening keynote, and will be giving a talk entitled <span class="Apple-style-span" style="font-family: inherit;">"Back To The Future of Open Source", looking issues around corporate involvement in Open Source projects, and what that means for the PostgreSQL community and contributors. Simon has been involved in many Open Source projects over the years, and was most recently known for his role at Sun Microsystems as Chief Open Source Officer.</span><br />
<span class="Apple-style-span" style="font-family: inherit;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: inherit;">The second, closing keynote will be given by Ed Boyajian, President and CEO of EnterpriseDB. Ed will be talking on "PostgreSQL's time to shine", and looking at how we as users and contributors to PostgreSQL have the opportunity to turn the $26B per year database industry on it's head.</span><br />
<span class="Apple-style-span" style="font-family: inherit;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: inherit;">This year's event will be held at the luxurious <a href="http://2010.pgday.eu/venue">Millenium Hotel</a> in Stuttgart, Germany, with the conference sessions on the 6th and 7th December, and training on the 8th December. We have a <a href="http://2010.pgday.eu/hotels">discount group rate</a> for hotel bookings that includes internet access and breakfast.</span><br />
<span class="Apple-style-span" style="font-family: inherit;"><br />
</span><br />
<a href="http://2010.pgday.eu/register">Registration</a> for the conference days and the training can be made online. We encourage you to register as early as possible to help us plan the event more effectively.<br />
<span class="Apple-style-span" style="font-family: inherit;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: inherit;">For more information on the conference, please visit the <a href="http://2010.pgday.eu/">website</a>.</span>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3645070705954691807.post-3642757425604844222010-11-04T00:27:00.000+00:002010-11-04T00:27:50.592+00:00PGWest: Day 1 and 2So my blogging of PGWest was a FAIL on the first day, as I never got around to following up my <a href="http://pgsnake.blogspot.com/2010/11/pgwest-day-0.html">Day 0 post</a> with anything, so with apologies, here's a quick roundup of day 1 and day 2.<br />
<br />
<b>Day 1 </b><br />
<b><br />
</b><br />
Started with breakfast with Magnus, Devrim and Guillaume before heading up to register on the mezzanine. The first half of the day was a number of three hour tutorials which were on some interesting topics, but none which particularly interested <i>me</i>, so I spent the time catching up with a number of colleagues who I haven't seen in a few months.<br />
<br />
After lunch, my talk on "Securing your web application" was one of the first 'normal' talks to be given. It was intended as a wide but shallow look at some of the security issues to consider when building a web app - a completely new talk which unfortunately didn't work as well as I'd hoped and needs some tweaking should I give it again; trimming the length a little, and focusing a little more on the database end of the stack. Still, I think it covered most of the important points for new developers to consider.<br />
<br />
After my talk, I spent more time with colleagues, including $BOSS who had arrived. I completely failed to see any more talks unfortunately.<br />
<br />
In the evening, we had a quick drink (thanks <a href="http://pugs.postgresql.org/sfpug">SFPUG</a>!) before heading off for dinner at an Irish pub, and then to the Starlight Room at the top of the hotel for a couple of drinks and dessert, coupled with lots of discussion on covering indexes (aka index-only scans), managing community workload and more.<br />
<br />
<b>Day 2</b><br />
<b><br />
</b><br />
Day 2 started with JD herding a handful of us together at breakfast to go and give Josh Berkus some encouragement in his talk on the PostgreSQL Community. An excellent talk for the newcomers in the room, though a little wasted on those of us who have been around the community for 10+ years.<br />
<br />
Highlight of the day was Scott McNealy's keynote, introduced by Ed Boyajian ($BIG_BOSS at EnterpriseDB). In case you've been living under a rock, you'll doubtless know that Scott was the founder and boss of Sun Microsystems until the Oracle takeover. The talk style was a little deadpan, but with a good stream of jokes that went down well with the audience. Interesting to hear Scott note that he was considered a good capitalist, whilst Larry Ellison is a great capitalist (who doesn't like to share)!<br />
<br />
Scott's talk was followed up with JD's introduction to the conference (yeah, halfway through the schedule - really Josh?), and then lunch. After a quick EnterpriseDB training meeting, I caught Jimbo's talk on <a href="http://sourceforge.net/projects/gridsql/">GridSQL</a>, Magnus' on database driven cache invalidation with Varnish (which we'll be using with the new PostgreSQL website backend, so I figured I should learn how it works), and then finally Bruce's new talk, MVCC Unmasked. Pretty complex for the newbie I suspect, but a very well presented topic.<br />
<br />
And on that note it's just about time to go back up to the Starlight Room, where the EnterpriseDB party is about to kick off with an open bar. I'll try not to get too hungover so I can write about day 3 tomorrow....Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3645070705954691807.post-58157043932226522172010-11-02T14:46:00.000+00:002010-11-02T14:46:01.212+00:00PGWest: Day 0...... or day 1, depending on how you look at it. Anyway, mostly yesterday, the day before the conference starts. Which is today. Obviously. $DEITY I hate jetlag-induced early mornings, especially when coupled with reminders for meetings on the East coast that go "ping" right as I'm finally drifting off to sleep again at 6AM.<br />
<br />
Anyway, enough about that. The flight from LHR to SFO was mostly uneventful, barring an APU failure which meant the HVAC was barely working until we took off. It got a little hot, but otherwise everything was good, and being completely cut off from email, twitter and IM for nearly 11 hours meant that I managed to get a bunch of work done that's been piling up for ages <b>and</b> watch the A-Team.<br />
<br />
Immigration at SFO was remarkably fast (I've only seen similar speeds in the past in Boston), which gave me plenty of time to queue up with a bunch of other passengers for over 10 minutes before a single cab showed up. What the *$£% is that about?<br />
<br />
Made it to the hotel, which took a little longer than expected due to the World Series (I'm sure I won't be the first, or the last to note that that's a <b>really</b> misleading name) to find a whole gaggle of elephant herders in the lobby, with beer. Oh, and one dolphin botherer (hi Rob!). Dinner was at a small, but excellent Indonesian place on Post and Jones where Greg Stark showed up from work to join myself, Aurynn Shaw, Bruce Momjian, Magnus Hagander, Jim Nasby, Rob Wultsch and Guillaume Lelarge.<br />
<br />
After dinner, a few of us stopped in the hotel bar for a night cap before heading off for some Zzzz's.<br />
<br />
Today the conference starts in earnest of course. Breakfast in 20 minutes, and then at 9AM, a bunch of 3 hour tutorials. After lunch, my talk "Securing your web application" (and overview for beginners) is up, then I have the rest of the conference to see what I like.<br />
<br />
So, there you have it. A couple of hundred largely useless words that spilled out of my brain having cleared out my "Starred Items" at 5AM leaving me nothing more interesting to do without committing more hours than I have. With any luck, tomorrow's roundup will be somewhat less of a waste of bytes, though someone did mention beer so I wouldn't hold your breath....Unknownnoreply@blogger.com0