\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-11-07-230106.jpg\")
<\/a>\nThere is no ambiguity there: only one service has the PostgreSQL name and logo. You cannot mistakenly select Aurora here. If you create an Amazon RDS PostgreSQL service, you have the “real” PostgreSQL. And you can even do that on the Free tier.<\/p>\n
You can check the version and compilation:<\/p>\n
\n$ PGHOST=database-1.ce5fwv4akhjp.eu-central-1.rds.amazonaws.com PGPORT=5432 PGPASSWORD=postgres psql -U postgres\n\npsql (12.4)\nSSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)\nType \"help\" for help.\n\npostgres=> select version();\n version\n--------------------------------------------------------------------------------------------------------\n PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit\n(1 row)\n<\/code><\/pre>\nThe only difference in the features is that, as it is a managed database, you don’t have all privileges:<\/p>\n
\npostgres=> \\du\n List of roles\n Role name | Attributes | Member of\n\n-----------------+------------------------------------------------------------+---------------------------------------------------\n postgres | Create role, Create DB +| {rds_superuser}\n | Password valid until infinity |\n rds_ad | Cannot login | {}\n rds_iam | Cannot login | {}\n rds_password | Cannot login | {}\n rds_replication | Cannot login | {}\n rds_superuser | Cannot login | {pg_monitor,pg_signal_backend,rds_replication,rds_password}\n rdsadmin | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}\n | Password valid until infinity |\n rdsrepladmin | No inheritance, Cannot login, Replication | {}\n\npostgres=> select * from pg_hba_file_rules;\n\n line_number | type | database | user_name | address | netmask | auth_method | options | error\n-------------+-------+---------------+------------+----------+---------+-------------+---------+-------\n 4 | local | {all} | {all} | | | md5 | |\n 10 | host | {all} | {rdsadmin} | samehost | | md5 | |\n 11 | host | {all} | {rdsadmin} | 0.0.0.0 | 0.0.0.0 | reject | |\n 12 | host | {rdsadmin} | {all} | all | | reject | |\n 13 | host | {all} | {all} | 0.0.0.0 | 0.0.0.0 | md5 | |\n 14 | host | {replication} | {all} | samehost | | md5 | |\n\n<\/code><\/pre>\nBut this is exactly the same as a PostgreSQL installed from the community sources where you are not the superuser.<\/p>\n
There are a few additional RDS specific libraries (which are not open source):<\/p>\n
\npostgres=> show shared_preload_libraries;\n\n shared_preload_libraries\n-----------------------------\n rdsutils,pg_stat_statements\n\npostgres=> select name,setting from pg_settings where name like 'rds.%';\n\n name | setting \n----------------------------------------+------------------------------------------------------------------------\n rds.extensions | address_standardizer, address_standardizer_data_us, amcheck, aws_commons, aws_s3, bloom, btree_gin, btree_gist, citext, cube, dblink, dict_int, dict_xsyn, earthdistance, fuzzystrmatch, hll, hstore, hstore_plperl, intagg, intarray, ip4r, isn, jsonb_plperl, log_fdw, ltree, orafce, pageinspect, pgaudit, pgcrypto, pglogical, pgrouting, pgrowlocks, pgstattuple, pgtap, pg_buffercache, pg_freespacemap, pg_hint_plan, pg_prewarm, pg_proctab, pg_repack, pg_similarity, pg_stat_statements, pg_transport, pg_trgm, pg_visibility, plcoffee, plls, plperl, plpgsql, plprofiler, pltcl, plv8, postgis, postgis_tiger_geocoder, postgis_raster, postgis_topology, postgres_fdw, prefix, rdkit, sslinfo, tablefunc, test_parser, tsm_system_rows, tsm_system_time, unaccent, uuid-ossp\n rds.force_admin_logging_level | disabled\n rds.force_autovacuum_logging_level | info\n rds.internal_databases | rdsadmin,template0\n rds.logical_replication | off\n rds.rds_superuser_reserved_connections | 2\n rds.restrict_logical_slot_creation | off\n rds.restrict_password_commands | off\n rds.superuser_variables | session_replication_role\n rds.tablespace_path_prefix | \/rdsdbdata\/db\/base\/tablespace\n<\/code><\/pre>\nThis is still the community edition that allows extensibility.
\nThere are also some additional functionalities, like RDS Performance Insights<\/a> to show the database activity with a time x-axis and active session y-axis, drilling down to wait events.<\/p>\nBy curiosity I’ve run the regression tests that are provided by the PostgreSQL distribution:<\/p>\n
\nPGHOST=database-1.ce5fwv4akhjp.eu-central-1.rds.amazonaws.com PGPORT=5432 PGPASSWORD=postgres psql -U postgres -c \"select version();\"\ncd \/var\/tmp\ngit clone --branch REL_12_STABLE https:\/\/github.com\/postgres\/postgres.git\ncd postgres\n.\/configure\ncd src\/test\/regress\nexport PGHOST=database-1.ce5fwv4akhjp.eu-central-1.rds.amazonaws.com\nexport PGPORT=5432\nexport PGPASSWORD=postgres\nexport PGUSER=postgres\nmake installcheck\n<\/code><\/pre>\nIt starts by setting some parameters, which fails for missing privileges:<\/p>\n
\n============== dropping database \"regression\" ==============\nDROP DATABASE\n============== creating database \"regression\" ==============\nCREATE DATABASE\nERROR: permission denied to set parameter \"lc_messages\"\ncommand failed: \"\/usr\/local\/pgsql\/bin\/psql\" -X -c \"ALTER DATABASE \\\"regression\\\" SET lc_messages TO 'C';ALTER DATABASE \\\"regression\\\" SET lc_m\nonetary TO 'C';ALTER DATABASE \\\"regression\\\" SET lc_numeric TO 'C';ALTER DATABASE \\\"regression\\\" SET lc_time TO 'C';ALTER DATABASE \\\"regressio\nn\\\" SET bytea_output TO 'hex';ALTER DATABASE \\\"regression\\\" SET timezone_abbreviations TO 'Default';\" \"regression\"\nmake: *** [installcheck] Error 2\n<\/code><\/pre>\nIn a managed database, the cloud provider needs to lockdown some administration commands in order to secure his platform, but we have the possibility to define those parameters by creating a parameter group. This is what I did in the console, and then removed those ALTER DATABASE from pg_regress.c:<\/p>\n
\nsed -ie 's\/\"ALTER DATABASE\/--&\/' pg_regress.c\n<\/code><\/pre>\nThen, I’m ready to run the regression tests:<\/p>\n
\n[opc@a regress]$ make installcheck\n\n...\n\n..\/..\/..\/src\/test\/regress\/pg_regress --inputdir=. --bindir='\/usr\/local\/pgsql\/bin' --dlpath=. --max-concurrent-tests=20 --schedule=.\/serial_schedule\n(using postmaster on database-1.ce5fwv4akhjp.eu-central-1.rds.amazonaws.com, port 5432)\n============== dropping database \"regression\" ==============\nDROP DATABASE\n============== creating database \"regression\" ==============\nCREATE DATABASE\n============== running regression test queries ==============\ntest tablespace ... FAILED 1406 ms\ntest boolean ... ok 684 ms\ntest char ... ok 209 ms\ntest name ... ok 311 ms\ntest varchar ... ok 201 ms\ntest text ... ok 549 ms\ntest int2 ... ok 354 ms\ntest int4 ... ok 580 ms\ntest int8 ... ok 954 ms\ntest oid ... ok 222 ms\ntest float4 ... FAILED 660 ms\ntest float8 ... FAILED 1136 ms\ntest bit ... ok 1751 ms\ntest numeric ... ok 5388 ms\n\n...\n\ntest hash_part ... ok 240 ms\ntest indexing ... FAILED 6406 ms\ntest partition_aggregate ... ok 1569 ms\ntest partition_info ... ok 620 ms\ntest event_trigger ... FAILED 1237 ms\ntest fast_default ... ok 1990 ms\ntest stats ... FAILED 643 ms\n\n======================================================\n 90 of 194 tests failed, 1 of these failures ignored.\n======================================================\n\nThe differences that caused some tests to fail can be viewed in the\nfile \"\/var\/tmp\/postgres\/src\/test\/regress\/regression.diffs\". A copy of the test summary that you see\nabove is saved in the file \"\/var\/tmp\/postgres\/src\/test\/regress\/regression.out\".\n<\/code><\/pre>\nThere are multiple tests that fail because of missing privileges. Actually, pg_regression expects to have all privileges. Using pg_regress is not a good idea for testing that the RDS PostgreSQL behaves like expected. It is not made for functional tests.<\/p>\n
PostgreSQL-like API in on RDS Aurora<\/h3>\n
This is where the confusion comes from. Aurora is a proprietary database built by Amazon. They started it by forking MySQL and modifying the storage layer in order to build a cloud-native database. Rather than storing the database files in EBS block storage attached to the database node where the instance is running, like all other RDS databases, the database service is split into separate (micro)services for the compute (EC2) and the storage (distributed over multiple AZ to provide High Availability, similar to the DynamoDB storage). Aurora code is not open-source and is very different from the community MySQL. More and more because of many improvements. Part of it is running on EC2 to parse, optimize and execute SQL statements and transactions, and update the buffers in cache. And part of it runs in the storage server which receives the redo to apply it on the data file blocks, which are distributed and shared with reader instances. This was in 2014 and Amazon always presented Aurora as another database engine in RDS. I’ve copy-pasted below a few archive.org snapshot of the https:\/\/aws.amazon.com\/rds\/<\/a> page if you want to look at the history. Amazon modified a lot the lower layers of the code but kept the upper layer to stay compatible with MySQL (version 5) in order to ease the application migration to Aurora. And this is how it is presented: Aurora is a new database with MySQL compatibility. Once you select the Aurora service in RDS, you can choose the version which mentions with MySQL version it is compatible with. For example, the latest version Aurora 2.09.. is labelled “Aurora (MySQL 5.7)”. Note that there are not only modification to adapt to the cloud native storage, but Amazon brings also some interesting improvement, unfortunately not given back to the open-source community.<\/p>\nHere are the screenshots from archive.org where you see from the begining that RDS for Aurora is different than RDS for MySQL. And when MySQL or PostgreSQL is mentioned with Aurora there’s always the “compatible” mention:
\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-11-08-180158.jpg\")
<\/a><\/p>\nWhat I mean by this is that, in my opinion, there was no ambiguity from Amazon about what is the open-source database and what is their proprietary engine.<\/p>\n
That’s a long story about the MySQL compatible Aurora because it was the only compatible API from 2014 to 2017. Then, as they had a strong storage engine, and a well layered code, they have built another flavor of Aurora taking the PostgreSQL upper layer to replace the MySQL one in Aurora. Then they have two proprietary databases: Aurora with MySQL compatibility and Aurora with PostgreSQL compatibility. As far as I know it has always been clear that it is only “compatibility” and Aurora has never been advertised to be a real PostgreSQL engine. They have RDS PostgreSQL for that. And I think they have different use cases. Aurora is probably the best choice when High Availability, scalability and elasticity is the most important. PostgreSQL may provide lower latency on block storage, and is probably cheaper (but Aurora serverless can also reduce the cost for rarely used databases).<\/p>\n
However, there is still some confusion and some Aurora users are asking the PostgreSQL community for help. The PostgreSQL community helps a lot their users because they know the engine (which is very well documented, and source code and source comments are accessible). But they cannot do anything for Aurora as we don’t even know what has been modified. As an example, I mentioned in a previous post<\/a> that some wait events are specific au Aurora – don’t ask the postgres mailing lists for that. I also think that the PostgreSQL community would appreciate that the improvements made by Amazon on the PostgreSQL code are shared with the community.<\/p>\nPostgreSQL on other public clouds<\/h3>\n
Not only is Amazon RDS PostgreSQL the real PostgreSQL from the community, but it is also in my opinion one of the most advanced managed service for it.<\/p>\n
\n- \n
\n- Oracle Cloud provides no managed service for PostgreSQL but only a Bitnami image<\/a>. I hope one day we will have a managed service with the same quality as the MySQL one.<\/li>\n
- Azure provides an old service running on Windows<\/a> but that will change as they bought Citus Data and hired well known PostgreSQL contributors.<\/li>\n
- Google Cloud DB has a decent managed service, but I need to look at their backups<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
.<\/p>\n
Talking about backups, Amazon RDS PostgreSQL has all features to avoid or recover from mistakes, which I listed in a 5-points tweet:<\/p>\n
\n5 critical features of managed DB services@awscloud<\/a> RDS PostgreSQL checks all five \ud83d\udc4d<\/p>\n1\/5\u231a\u2705be able to recover to a point-in-time beyond the last backup (any point-in-time within the retention) pic.twitter.com\/s0siA3RKJC<\/a><\/p>\n— Franck Pachot (@FranckPachot) November 8, 2020<\/a><\/p><\/blockquote>\n