Magpie offers the PostgreSQL data source, which provides users the ability to connect to PostgreSQL databases through standard JDBC connections or through the Copy Manager. This is the preferred method for accessing PostgreSQL databases in Magpie as this datasource yields much faster performance for large scale reads and writes when accessing databases through the Copy Manager.
Postgres sources are configured very similarly to standard JDBC data sources in Magpie. Please view Data Source JSON specification to see more details. The configuration is set during the initial creation of the source or through an
ALTER statement using a JSON specification to set the values.
An example of such a command is shown below.
Once a Postgres source has been created, the tables within it can be used as the underlying storage for tables within Magpie. The commands present in the "Accessing Data from JDBC Sources" page are broadly applicable to PostgreSQL data sources as well.
Note that the PostgreSQL data source does not support array type fields when processing through the Copy Manager. To use fields in PostgreSQL databases containing array types users must not use the
bulk access option when creating tables and schemas and must specify the
jdbc persistence mapping type in save commands. This will force Magpie to connect to the database using JDBC rather than the Copy Manager. An example of specifying the persistence mapping type on a save command is below.
To access this type of data source, you may need to adjust the security configuration of your cloud environment. In particular, you need to generate database user credentials for the Magpie cluster, along with appropriate permissions to access relevant data. Please reach out to a member of the Silectis team with any questions or support requests.
Note, database data sources should generally be created using a user with read-only permissions to the target database. This can prevent unwanted updates to the target database resulting from actions within Magpie. If you do need to write data back to the database, it may be useful to define a separate data source to be used for writing, with stricter permissions.