Postgres Plus® Advanced Server FAQ
Frequently Asked Questions
Why does Advanced Server need root (or administrator) permissions to install?
The installation files are owned by root for security reasons. If they were owned by the service user (enterprisedb), and the database server were to be compromised somehow (through a bug or user code), then the attacker would be able to replace the database server binaries, potentially allowing installation of a trojan to enable further attacks. With the binaries owned by root, anyone accessing the server through Advanced Server (which is running as the enterprisedb user) is unable to modify them.
What is the difference between PostgreSQL and Postgres Plus Advanced Server?
Advanced Server is built upon PostgreSQL and has additional functionality and capabilities in the following areas:
- Advanced Performance features like Index Advisor, Query Hints, Infinite Cache, DynaTune, and a High speed loader.
- Oracle Features such as User Defined Objects, Parallel Bulk Loader, Oracle Database Links, Bulk Collect/Bind and Multi-threaded Replication.
- Oracle Tools such as Oracle Dictionary Views, EDB*Plus, EDB*Wrap, and SQL wait statistics.
- Oracle Compatibility for PL/SQL support, PL/SQL Debugger, Oracle SQL Extensions, OCI, and popular function packages.
- Management Tools: Software Update Monitor, SQL Performance Profiler, Migration Toolkit, Postgres Enterprise Manager, SQL Protect, and xDB Replication Server.
For more differences details see the full feature comparison chart.
What are the product maintenance and support lifecycle timelines for Postgres Plus Advanced Server versions?
In general each version (change in number to the right of the decimal point) is supported for 3 years with an additional 2 years of extended End of Life (EOL). For specific version dates see the Advanced Server Platform Products Lifecycle page.
Is EDB*Wrap compatible with Oracle's PL/SQL wrapper capability?
EDB*Wrap provides comparable stored procedure obfuscation to Oracle's wrap capability. However, it is not compatible. i.e. Oracle wrapped stored procedures will not run in Advanced Server until re-wrapped with EDB*Wrap.
Does Postgres Plus Advanced Server support Spatial data?
Yes, via the PostGIS module bundled with Postgres Plus. PostGIS adds support for geographic objects to the Postgres Plus Advanced Server / PostgreSQL object-relational database. In effect, PostGIS "spatially enables" the Postgres Plus Advanced Server /PostgreSQL server, allowing it to be used as a backend spatial database for geographic information systems (GIS).
What type of replication does Advanced Server support?
Advanced Server supports built-in native binary replication. Advanced Server also includes a trigger based replication solution (xDB Replication Server) that moves data between Advanced Server, Oracle, PostgreSQL, and Microsoft SQL Server databases.
Does Postgres Plus Advanced Server support clustered databases?
Yes. Postgres Plus Advanced Server supports and is deployed in clustered configurations at many customers. Clustering software such as LinuxHA or Red Hat® Cluster Suite is necessary to setup this Active/Passive cluster.
What high availability (HA) solutions does Postgres Plus Advanced Server support?
Postgres Plus Advanced Server supports the following HA solutions:
- Active/Passive clustering requiring clusterware like LinuxHA or Red Hat Cluster Server,
- Hot Standby database for Disaster Recovery
- Pgpool (connection pool management)
Advanced Server (as well as PostgreSQL) are also available in Postgres Plus Cloud Database which provides instant highly available elastic database clusters in the cloud complete with streaming replication, connection pooling, load balancing and automatic backups.
Does Advanced Server support key-value store applications?
Yes, you can store and manipulate key-values in Advanced Server or PostgreSQL. This article in PostgreSQL Magazine The key-value store everyone ignored gives a quick overview of the feature.
Does Postgres Plus Advanced Server support/have a DataGuard feature?
Yes. Postgres Plus Advanced Server has Hot Standby Database support. It is similar to Oracle's physical standby database where the log files are archived and shipped to a remote site where a standby database is in constant recovery mode until a disaster occurs and a failover is performed. This standby server may also be used for read transactions.
Does Advanced Server support database links?
Yes. There is currently support for database links between Postgres Plus Advanced Server databases and between Postgres Plus Advanced Server and Oracle databases.
What version of Oracle is Postgres Plus Advanced Server compatible with?
Postgres Plus Advanced Server is not compatible with a specific version of Oracle. Instead, Postgres Plus Advanced Server is compatible with the core functionality available in Oracle SQL Extensions and PL/SQL, as well as the most commonly used features. Postgres Plus Advanced Server can migrate/run applications that were developed in Oracle 7, 8, 8i, 9i, 10g and 11g.
Does Postgres Plus Advanced Server support ROWNUM?
Yes. It also supports SYSDATE, DUAL, decode(), NVL, definer/invoker rights, and many more Oracle features. Contact EnterpriseDB for documentation detailing all the Oracle compatibility features.
What is Postgres Studio?
Postgres Studio is part of Advanced Server and is an enterprise-class, cross-platform developer and DBA console that users of Oracle database tools can use with virtually no re-training. Postgres Studio users can:
- Browse multiple EnterpriseDB and Postgres databases simultaneously
- Conduct sophisticated SQL query profiling and analysis
- Dynamically monitoring database performance to optimize and tune Postgres Plus Advanced Server
- Use pre-configured wizards for security, backup, and restore
What is Infinite Cache?
Infinite Cache allows you to increase the amount of data maintained as in-memory cache by distributing the cache across existing inexpensive commodity hardware farms. Compression of cache data allows storing entire databases in a durable fashion for lightning fast performance. By storing more or all data in a RAM based cache there are fewer disk operations which are much slower. Infinite Cache is also completely transparent to applications and requires no special caching code by application developers to safeguard the durability of any write operations.
How does Infinite Cache compare to caching everything in the application server?
While traditionally, the architecture of caching everything at the application tier has been effective, maintaining the cache has been difficult to code and burdensome. Additional development is necessary in the application to properly synchronize the cache with the data in the database and it limits the overall data flexibility by restricting how the data in the backend database is changed.
I have a transactional system, will Infinite Cache still help?
Yes. Infinite Cache is most effective in a read-mostly environment, but the performance benefits are still tangible as transactions are mixed into the workload. For example, even in more extreme transactional cases such as a 50-50 read vs. write mix, Infinite Cache still provides a 2X performance gain.
How does Infinite Cache handle updates to data?
Updates to data are handled transparently with Infinite Cache's patent pending algorithms. All database transactions are respected. Blocks of changed data are updated into Infinite Cache as they are ejected from the pg-buffercache via the normal LRU algorithms. This simple algorithm insures high efficiency, transactional integrity and end user transparency.
How big (memory) can I scale Infinite Cache?
Infinite Cache can scale to many terabytes and beyond.
Is there a per socket fee for Infinite Cache blade machines utilized by Postgres Plus Advanced Server in an Infinite Cache configuration?
No, there is no additional cost. Customers may have as many cache blade machines as they want associated with a properly licensed Postgres Plus Advanced Server.
What kind of server should I use for my Infinite Cache servers?
Any commodity hardware can be used. The need for CPU and disk IO is minimal with heavy reliance on memory.
What kind of network connectivity is needed between the database and Infinite Cache servers?
With Infinite Cache, the faster the network, the better. Performance does increase when using faster network infrastructures such as Infiniband, but simple Gigabit Ethernet is a capable alternative.
What happens if one of the Infinite Cache servers goes offline?
If one of the Infinite Cache servers goes offline momentarily such as a network interruption, Postgres Plus Advanced Server will queue the cache update until the cache server comes back online for the write. If the outage continues for a prolonged period of time, the Infinite Cache server will be marked offline and other Infinite Cache servers will absorb the load until an administrator restores the downed server to health.
Can I dynamically add Infinite Cache servers to the database?
Yes. since the release of Advanced Server v9.0, you can dynamically add Infinite Cache blades without stopping your cluster.
Do the Infinite Cache servers need to be running the same operating system as the database server?
No, Your database server could be running on Windows, Linux, Mac or Solaris, but initially the Infinite Cache servers need to be running on Linux. Any of the supported Linux distributions such as RHEL and SLES cache can be used on the cache servers.
How much Infinite Cache do I need in relation to the size of my database?
The default settings of Infinite Cache compresses the data as it is put into the cache. This allows the cache to be significantly smaller than the physical data size. Depending on the data contained in the database, the cache can be up to 10 times smaller then the physical size.
What special caching code needs to be included in my client applications to use Infinite Cache?
None. Infinite Cache is completely transparent to the calling application. Data durability is handled automatically by the Infinite Cache architecture.
What is the difference between GridSQL and Infinite Cache?
Both provide horizontal scaling architectures. GridSQL is appropriate for extremely large data warehousing applications and addresses distributing physical data storage and parallel queries across multiple machines. Infinite Cache is targeted at OLTP workloads with lots of random reads and addresses distributing fast performing in-memory cache data across multiple machines.
Does Quest® Toad® work with Postgres Plus Advanced Server?
Quest has a version of Toad called Toad for Data Analysts that works with Postgres Plus Advanced Server.
Does Postgres Plus Advanced Server work with the JBoss application server?
More Frequently Asked Questions...
Still have more questions? See All Frequently Asked Questions.
This article is translated to Serbo-Croatian by Anja Skrba from Webhostinggeeks.com.