-
Notifications
You must be signed in to change notification settings - Fork 221
/
postgresql.ad
98 lines (71 loc) · 3.74 KB
/
postgresql.ad
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
=== PostgreSQL
Starting with PostgreSQL 8.3, SymmetricDS supports the transaction identifier.
Binary Large Object (BLOB) replication is supported for both byte array (BYTEA) and object ID (OID) data types.
In order to function properly, SymmetricDS needs to use session variables.
Before PostgreSQL 9.2, session variables are enabled using a custom variable class. Add the following line to the postgresql.conf file of PostgreSQL server on versions before 9.2:
----
custom_variable_classes = 'symmetric'
----
This setting is required on versions before 9.2, and SymmetricDS will log an error and exit if it cannot set session variables.
PostgreSQL versions 9.2 or later do not require this setting.
Before database triggers can be created by in PostgreSQL, the plpgsql language handler must be installed on the database.
If plpgsql is not already installed, the following statements can be run by the administrator on the database:
[source, SQL]
----
CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS
'$libdir/plpgsql' LANGUAGE C;
CREATE FUNCTION plpgsql_validator(oid) RETURNS void AS
'$libdir/plpgsql' LANGUAGE C;
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
HANDLER plpgsql_call_handler
VALIDATOR plpgsql_validator;
----
If you want SymmetricDS to install into a schema other than public you can alter the database user to set the default schema.
[source, SQL]
----
alter user {user name} set search_path to {schema name};
----
You will also need to set privileges on the schema so that the SymmetricDS user can use and write to the schema.
[source, SQL]
----
grant usage on schema {schema name} to {user name};
grant create on schema {schema name} to {user name};
----
==== Permissions
If SymmetricDS is connected into a different schema than the application tables and a different user account is used to write to the the application tables the following permissions need to be granted to the application user to access the SymmetricDS schema
[source, Sql]
----
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA {SYMMETRIC_SCHEMA} TO {APPLICATION USER}
GRANT ALL ON SCHEMA {SYMMETRIC_SCHEMA} TO {APPLICATION USER}
GRANT ALL ON ALL SEQUENCES IN SCHEMA {SYMMETRIC_SCHEMA} TO {APPLICATION USER}
GRANT ALL ON ALL FUNCTIONS IN SCHEMA {SYMMETRIC_SCHEMA} TO {APPLICATION USER}
----
ifdef::pro[]
==== Log Miner
When using Log Miner instead of triggers, it will use the logical decoding feature introduced in PostgreSQL version 9.4.
The WAL level should be set to "logical" on the Postgres server.
Edit postgresql.conf and restart Postgres after changing:
wal_level = logical
max_replication_slots = 10
Replication of updates and deletes work as expected for tables with a primary key.
For tables without a primary key, the user needs to set the REPLICA IDENTITY on the table to either USING INDEX
to record columns from the named index or FULL to record all columns of the row.
In Postgres 10 and newer, it will prevent updates and deletes with an error about setting REPLICA IDENTITY.
alter table mytable replica identity full;
endif::pro[]
.supported data types
|===
|Data Type|Supported?
|SmallInt, Integer, BigInt|Yes
|Decimal, Numeric|Yes
|Real, Double Precesion|Yes
|Serial, BigSerial|Yes
|Char, Varchar, Text|Yes
|Money|No
|Timestamp, Date, Time, Interval|Yes
|Enum|No
|Point, Lseg, Box, Path, Polygon, Circle|Yes
|===
==== Bulk Loading
SymmetricDS has bulk loading capability available for Postgres, when registering a PostgreSQL database with SymmetricDS, bulk loading will be checked by default. SymmetricDS specifies data loader types on a channel by channel basis.
To utilize Postgres Bulk loading versus straight JDBC insert, specify the Postgres Bulk Loader ("postgres_bulk") in the data_loader_type column of sym_channel.