Compatibility with PostgreSQL

Redrock Postgres is developed based on PostgreSQL. Redrock Postgres is compatible with PostgreSQL on client protocols and SQL syntax. If your application is developed based on PostgreSQL, then:

• Based on the binary compatibility with PostgreSQL on client protocols, Redrock Postgres can be accessed using the same drivers as PostgreSQL for various programming languages, such as Java, C/C++, .Net, Python, Ruby, Perl, Go, ODBC.
• Due to SQL compatibility with PostgreSQL, you can migrate the application software to Redrock Postgres without any modification.
• You can use PostgreSQL-related desktop management tools to access Redrock Postgres, such as DBeaver, pgAdmin 4, and Navicat for PostgreSQL.

That means in most cases, you can use pg_dump to export data from PostgreSQL, uninstall PostgreSQL and install Redrock Postgres, import the exported data into Redrock Postgres, and you are good to go.

In PostgreSQL, roles are instance-level objects, while in Redrock Postgres, roles are database-level objects. That means when you access different database objects in the same instance, you may see roles with the same name, in fact, they are different role objects belonging to different databases.

DBA should pay attention to this difference, while developers may not.

In PostgreSQL, tablespaces are instance-level objects, while in Redrock Postgres, tablespaces are database-level objects. That means when you access different database objects in the same instance, you may see tablespaces with the same name, in fact, they are different tablespace objects in the respective databases.

DBA should pay attention to this difference, while developers may not.

Some statistics views, such as pg_stat_activity and pg_locks, contain fields named pid. In PostgreSQL, these fields indicate the operating system process ID. In Redrock Postgres, these fields indicate the database process ID.

There is a field named spid in pg_stat_activity view, indicating the actual operating system process ID.

• VACUUM FULL is deprecated and only syntax remains

  Redrock Postgres introduces the undo log. When tuples are modified or deleted, the records of the old version are stored in the undo segment, implementing in-place update. Therefore, data records in tables do not need to be reorganized.

• VACUUM FREEZE is deprecated and only syntax remains

  In Redrock Postgres, the transaction ID type xid consists of the undo segment number, transaction slot number, and sequence number. Transaction IDs will not be reused. Therefore, tuples in table do not need to be frozen.

• You cannot specify the TABLESPACE option in CREATE DATABASE command

  You can specify the storage location of the database in the CREATE DATABASE command to create a new database. Eg: CREATE DATABASE name LOCATION ‘directory’.

• You cannot specify the OWNER option in CREATE DATABASE command

  After you have created the database, you can login to the newly created database and execute the ALTER DATABASE OWNER command to modify the database owner.

• ALTER DATABASE TABLESPACE is deprecated

  You can run the CREATE DATABASE command to specify the original database as the template database and specify the storage location of the new database to create a new database. Eg: CREATE DATABASE newname TEMPLATE dbname LOCATION ‘directory’.

• ALTER DATABASE OWNER cannot be executed in databases other than the current database

  You can login to the database to be changed and run the command to change the database owner.

• You cannot specify the IN DATABASE option in ALTER ROLE command

  You can login to the database to be changed and run the command to change the database role.

• Serializable Isolation Level are not supported

  Use standard read committed and repeatable read isolation levels.

• SP-GiST Indexes are not supported

  You can replace it with an index of type GiST.

• System Columns related to transaction (xmin, cmin, xmax, cmax) are not supported

  In Redrock Postgres, the corresponding system columns are rowxid, rowcid, and rowtime.

If you are using any of the following parameters in your postgresql.conf file you should remove or rename them.

• vacuum_freeze_table_age is removed
• vacuum_freeze_min_age is removed
• vacuum_multixact_freeze_table_age is removed
• vacuum_multixact_freeze_min_age is removed
• vacuum_cleanup_index_scale_factor is removed
• vacuum_cost_delay is removed
• vacuum_cost_page_hit is removed
• vacuum_cost_page_miss is removed
• vacuum_cost_page_dirty is removed
• vacuum_cost_limit is removed
• autovacuum renamed to autoanalyze
• log_autovacuum_min_duration renamed to log_autoanalyze_min_duration
• autovacuum_max_workers renamed to autoanalyze_max_workers
• autovacuum_naptime renamed to autoanalyze_naptime
• autovacuum_vacuum_threshold is removed
• autovacuum_analyze_threshold renamed to autoanalyze_base_threshold
• autovacuum_vacuum_scale_factor is removed
• autovacuum_analyze_scale_factor renamed to autoanalyze_scale_factor
• autovacuum_freeze_max_age is removed
• autovacuum_multixact_freeze_max_age is removed
• autovacuum_vacuum_cost_delay is removed
• autovacuum_vacuum_cost_limit is removed
• autovacuum_work_mem is removed
• db_user_namespace is removed. In Redrock Postgres, users are inherently database-level objects.
• old_snapshot_threshold is removed
• track_commit_timestamp is removed. The function pg_xact_commit_timestamp is always available.

If you are using any of the following storage parameters in your table definitions you should remove or rename them.

• autovacuum_enabled renamed to autoanalyze_enabled, toast.autovacuum_enabled renamed to toast.autoanalyze_enabled
• vacuum_index_cleanup, toast.vacuum_index_cleanup is removed
• vacuum_truncate, toast.vacuum_truncate is removed
• autovacuum_vacuum_threshold, toast.autovacuum_vacuum_threshold is removed
• autovacuum_vacuum_scale_factor, toast.autovacuum_vacuum_scale_factor is removed
• autovacuum_analyze_threshold renamed to autoanalyze_base_threshold
• autovacuum_analyze_scale_factor renamed to autoanalyze_scale_factor
• autovacuum_vacuum_cost_delay, toast.autovacuum_vacuum_cost_delay is removed
• autovacuum_vacuum_cost_limit, toast.autovacuum_vacuum_cost_limit is removed
• autovacuum_freeze_min_age, toast.autovacuum_freeze_min_age is removed
• autovacuum_freeze_max_age, toast.autovacuum_freeze_max_age is removed
• autovacuum_freeze_table_age, toast.autovacuum_freeze_table_age is removed
• autovacuum_multixact_freeze_min_age, toast.autovacuum_multixact_freeze_min_age is removed
• autovacuum_multixact_freeze_max_age, toast.autovacuum_multixact_freeze_max_age is removed
• autovacuum_multixact_freeze_table_age, toast.autovacuum_multixact_freeze_table_age is removed
• log_autovacuum_min_duration renamed to log_autoanalyze_min_duration, toast.log_autovacuum_min_duration renamed to toast.log_autoanalyze_min_duration