Postgresql ma'lumotlar bazasidan aws dms uchun manba sifatida foydalanish


Download 50.97 Kb.
Sana30.11.2019
Hajmi50.97 Kb.

PostgreSQL ma'lumotlar bazasidan AWS DMS uchun manba sifatida foydalanish

Siz bitta yoki bir nechta PostgreSQL ma'lumotlar bazasidan ma'lumotlarni AWS DMS yordamida ko'chirishingiz mumkin. Manba sifatida PostgreSQL ma'lumotlar bazasi yordamida siz boshqa PostgreSQL ma'lumotlar bazasiga yoki boshqa qo'llab-quvvatlanadigan ma'lumotlar bazalaridan biriga ko'chirishingiz mumkin. AWS DMS PostgreSQL 9.4 va undan keyingi versiyalarini (9.x versiyalari uchun), 10.x va 11.x ma'lumotlar bazalarini ushbu turdagi ma'lumotlar bazasi uchun manba sifatida qo'llab-quvvatlaydi:



  • Mahalliy ma'lumotlar bazalari

  • EC2 nusxasidagi ma'lumotlar bazalari

  • Amazon RDS DB nusxasida ma'lumotlar bazalari

  • PostgreSQL muvofiqligi bilan Amazon Aurora sMB ma'lumotlar bazasida ma'lumotlar bazasi

Eslatma

PostgreSQL 11.x versiyalari faqat AWS DMS 3.3.0 va undan keyingi versiyalarida manba sifatida qo'llab-quvvatlanadi. PostgreSQL 9.4 va undan keyingi versiyalarini (9.x versiyalari uchun) va 10.x har qanday DMS versiyasida manba sifatida ishlatishingiz mumkin.



Eslatma

PostgreSQL 10.x versiyalari oldingi versiyalardan funktsiyalar nomlari va papkalar nomlaridagi ko'plab o'zgarishlarni o'z ichiga oladi.

Ba'zi hollarda, siz PostgreSQL 10.x ma'lumotlar bazasini manba sifatida va AWS DMS 3.3.0 versiyasidan oldinroq foydalanishingiz mumkin. Bunday hollarda, ma'lumotlar bazangizni AWS DMS uchun manba sifatida tayyorlash to'g'risida ma'lumot olish uchun PostgreSQL Version 10.x-ni AWS DMS uchun manba sifatida foydalanishga qarang .

Agar siz PostgreSQL 10.x ma'lumotlar bazasini manba sifatida va DMS 3.3.0 yoki undan keyingi versiyalaridan foydalansangiz, DMS-ning avvalgi versiyalari bilan 10.x ma'lumot bazalari uchun tayyorgarlik ko'rmang .

PostgreSQL-ning qo'llab-quvvatlanadigan manba versiyalaridan foydalanish uchun DMS versiyasining talablari to'g'risida qisqacha ma'lumot uchun quyidagi jadvalga qarang.


PostgreSQL manba versiyasi

AWS DMS ishlatiladigan versiyasi

9.x

Mavjud bo'lgan DMS versiyasidan foydalaning.

10.x

Agar 3.3.0 dan oldin DMS versiyasini ishlatsangiz, PostgreSQL Version 10.x-da AWS DMS-ning manbai sifatida foydalanish-da tavsiflangan o'rash funktsiyalari yordamida PostgreSQL manbasini tayyorlang .

Agar DMS 3.3.0 yoki undan keyingi versiyasidan foydalansangiz, ushbu funktsiyani yaratmang. PostgreSQL manbasini hech qanday qo'shimcha tayyorgarliksiz ishlatishingiz mumkin.



11.x

DMS 3.3.0 versiyasidan foydalaning.

PostgreSQL tugatish nuqtasi va replikatsiya nusxasi o'rtasidagi aloqalarni shifrlash uchun SSL-dan foydalanishingiz mumkin. PostgreSQL so'nggi nuqtasi bilan SSL-dan foydalanish haqida ko'proq ma'lumot olish uchun SSL- dan AWS ma'lumotlar bazasini Migratsiya xizmati bilan foydalanish- ga qarang .

PostgreSQL ma'lumotlar bazasidan PostgreSQL ma'lumotlar bazasiga AWS-da bir hil ko'chish uchun quyidagilar to'g'ri bo'ladi.



  • JSONB columns on the source are migrated to JSONB columns on the target.

  • JSON columns are migrated as JSON columns on the target.

  • HSTORE columns are migrated as HSTORE columns on the target.

For a heterogeneous migration with PostgreSQL as the source and a different database engine as the target, the situation is different. In this case, JSONB, JSON, and HSTORE columns are converted to the AWS DMS intermediate type of NCLOB and then translated to the corresponding NCLOB column type on the target. In this case, AWS DMS treats JSONB data as if it were a LOB column. During the full load phase of a migration, the target column must be nullable.

AWS DMS supports change data capture (CDC) for PostgreSQL tables with primary keys. If a table doesn't have a primary key, the write-ahead logs (WAL) don't include a before image of the database row and AWS DMS can't update the table.

DW nusxasi mantiqiy replikatsiyadan foydalanishga sozlanganda AWS DMS Amazon RDS PostgreSQL ma'lumotlar bazasida CDC-ni qo'llab-quvvatlaydi. Amazon RDS PostgreSQL DB 9.4.9 va undan yuqori versiyalari, 9.5.4 va undan yuqori versiyalari uchun mantiqiy replikatsiyani qo'llab-quvvatlaydi. Amazon RDS, shuningdek, PostgreSQL 10.6 muvofiqligi bilan 2.2.0 va 2.2.1 versiyalari yordamida Amazon Aurora DB nusxasi uchun mantiqiy replikatsiyani qo'llab-quvvatlaydi.

PostgreSQL ma'lumotlar bazalari va AWS DMS bilan ishlash bo'yicha qo'shimcha ma'lumotlar uchun quyidagi bo'limlarga qarang.



Mavzular

  • PostgreSQL-dan PostgreSQL-ga AWS DMS-dan o'tish

  • PostgreSQL ma'lumotlar bazasidan AWS DMS uchun manba sifatida foydalanish uchun zarur shartlar

  • PostgreSQL ma'lumotlar bazasini AWS DMS manbai sifatida ishlatishda xavfsizlik talablari

  • PostgreSQL ma'lumotlar bazasidan AWS DMS uchun manba sifatida foydalanishda cheklovlar

  • Amazon RDS PostgreSQL ma'lumotlar bazasini manba sifatida o'rnatish

  • PostgreSQL manbalar bazasidan AWS DMS artefaktlarini olib tashlash

  • PostgreSQL ma'lumotlar bazasidan AWS DMS uchun manba sifatida foydalanishda qo'shimcha sozlash

  • PostgreSQL Version 10.x dan AWS DMS uchun manba sifatida foydalanish

  • PostgreSQL-ni AWS DMS uchun manba sifatida foydalanishda qo'shimcha ulanish xususiyatlari

  • PostgreSQL uchun manbali ma'lumot turlari

PostgreSQL-dan PostgreSQL-ga AWS DMS-dan o'tish

Turli xil migratsiya uchun siz PostgreSQL-dan boshqa ma'lumotlar bazasi tizimidan PostgreSQL ma'lumotlar bazasiga ko'chib o'tayotganingizda, AWS DMS deyarli har doim ishlatiladigan eng yaxshi ko'chib yurish vositasidir. PostgreSQL ma'lumotlar bazasidan PostgreSQL ma'lumotlar bazasiga o'tkazilayotgan bir hil migratsiya uchun mahalliy vositalar samaraliroq bo'lishi mumkin.

PostgreSQL ma'lumotlar bazasini ko'chirish vositalaridan pg_dumpquyidagi sharoitlarda foydalanishingizni tavsiya qilamiz :


  • Siz bir hil migratsiyaga egasiz, bu erda siz PostgreSQL manbasidan maqsadli PostgreSQL ma'lumotlar bazasiga o'tkazasiz.

  • Siz butun ma'lumotlar bazasini ko'chirmoqdasiz.

  • Mahalliy vositalar sizga minimal ish vaqti bilan ma'lumotlarni ko'chirishga imkon beradi.

pg_dumpFoyda bir PostgreSQL ma'lumotlar bazasi bir diagramma va ma'lumotlar borini yaratish copy buyrug'i foydalanadi. Damp skripti pg_dump ma'lumotlar bazasiga xuddi shu nomdagi ma'lumotlarni yuklaydi va jadvallar, indekslar va tashqi kalitlarni qayta yaratadi. Ma'lumotlar bazasini boshqa nom bilan tiklash uchun siz pg_restorebuyruq va -dparametrdan foydalanishingiz mumkin .

PostgreSQL yoki Amazon Aurora (PostgreSQL) uchun PostgreSQL ma'lumotlar bazasini Amazon RDS-ga kiritish haqida qo'shimcha ma'lumot olish uchun https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide//PostgreSQL.Procedural.Importing.html-ga qarang .



PostgreSQL-dan ma'lumotlarni PostgreSQL-ga o'tkazish uchun DMS-dan foydalanish

AWS DMS ma'lumotlarni, masalan, PostgreSQL yoki Amazon Aurora (PostgreSQL) misollari uchun Amazon RDS-ning maqsadli bazasida joylashgan manba ma'lumotlar PostgreSQL-dan ko'chirishi mumkin. PostgreSQL asosiy yoki asosiy ma'lumotlar turlari ko'pincha muvaffaqiyatli ko'chiriladi.

Manba ma'lumotlar bazasida qo'llab-quvvatlanadigan, ammo maqsadli ravishda qo'llab-quvvatlanmaydigan ma'lumotlar turlari muvaffaqiyatli ko'chirilmasligi mumkin. Agar ma'lumot turi noma'lum bo'lsa, AWS DMS ba'zi ma'lumot turlarini satrlar sifatida uzatadi. XML va JSON kabi ba'zi ma'lumot turlari kichik fayllar sifatida muvaffaqiyatli ko'chirilishi mumkin, ammo agar ular katta hujjatlar bo'lsa muvaffaqiyatsiz bo'lishi mumkin.

Quyidagi jadvalda PostgreSQL ma'lumotlar manbalari va ularni muvaffaqiyatli ko'chirish mumkinligi ko'rsatilgan:



Ma'lumot turi

Migratsiya muvaffaqiyatli amalga oshirildi

Qisman ko'chib ketadi

Ko'chib o'tmaydi

Izohlar

INTEGER

X










SMALLINT

X










BIGINT

X










Raqam / DECIMAL (p, s)




X




0

Raqamli / qaror




X




p> 38 yoki p = s = 0

HAQIQIY

X










QO'ShIMChA

X










Kichik

X










SERIAL

X










BIGSERIAL

X










PUL

X










CHAR




X




Belgilangan aniqliksiz

CHAR (n)

X










VARCHAR




X




Belgilangan aniqliksiz

VARCHAR (n)

X










TEXT

X










BYTEA

X










TIMESTAMP

X










TIMESTAMP (Z)




X







DATE

X










TIME

X










TIME (z)




X







INTERVAL




X







BOOLEAN

X










ENUM







X




CIDR







X




INET







X




MAKADDR







X




TSVECTOR







X




TSQUERY







X




XML




X







POINT







X




LINE







X




LSEG







X




BOX







X




PATH







X




POLIGON







X




QURILMA







X




JSON




X







ARRAY







X




Tarkibi







X




RANG







X




Eslatma

Agar PostgreSQL NUMERIC (p, s) ma'lumot turida aniqlik va o'lchov aniqlanmasa, AWS DMS sukut bo'yicha 28 va 6 o'lchovli shkalalardan foydalanadi (NUM, 28,6). Masalan, manbadan 0.611111104488373 qiymati PostgreSQL maqsadidagi 0.611111-ga o'zgartiriladi.



PostgreSQL ma'lumotlar bazasidan AWS DMS uchun manba sifatida foydalanish uchun zarur shartlar

PostgreSQL ma'lumotlar bazasi AWS DMS uchun manba bo'lishi uchun quyidagilarni bajaring:



  • PostgreSQL ma'lumotlar bazasidan 9.4.x yoki undan keyingi versiyadan foydalaning.

  • Change Data Capture (CDC) yoki CDC-vazifalarini to'liq yuklash uchun, PostgreSQL manbali ma'lumotlar bazasida ko'rsatilgan foydalanuvchi hisobi uchun superuserga ruxsat bering. Manba replikatsiya uchun maxsus funktsiyalarga kirish uchun superuser ruxsatlari talab qilinadi. Faqat to'liq vazifalarni bajarish uchun ularni ko'chirish uchun SELECT ruxsatnomalari talab qilinadi.

  • AWS DMS replikatsiya serverining IP manzilini pg_hba.confkonfiguratsiya fayliga qo'shing .

  • postgresql.confKonfiguratsiya faylida quyidagi parametrlar va qiymatlarni o'rnating :

    • O'rnatish wal_level = logical

    • max_replication_slots1 dan katta qiymatga sozlang .

max_replication_slotsQiymati ishlatish uchun kerakli deb vazifalar soniga ko'ra belgilash lozim. Masalan, beshta vazifani bajarish uchun kamida beshta uyani belgilash kerak. Vazifalar vazifa boshlanishi bilanoq avtomatik ravishda ochiladi va hatto boshqa ishlamayotgan bo'lsa ham ochiq qoladi. Ochiq uyalarni qo'lda yo'q qilishingiz kerak.

    • max_wal_senders1 dan katta qiymatga sozlang .

max_wal_sendersParametr ishlatish mumkin, bir davrda va vazifalar sonini belgilab beradi.

    • O'rnatish wal_sender_timeout =0

wal_sender_timeoutParametr harakatsiz uzoq millisekundlarda belgilangan miqdordagi ortiq bo'lgan replikatsiya ulanishlarni tugatyapti. Standart 60 soniya bo'lsa ham, ushbu parametrni nolga belgilashingizni maslahat beramiz, bu esa kutish vaqti mexanizmini o'chiradi.

  • idle_in_transaction_session_timeoutPostgreSQL 9.6 va undan keyingi versiyalaridagi parametr vaqtni bekor qilish va ishlamay qolishga imkon beradi. AWS DMS vositasi ularni qayta ishlatishdan oldin ba'zi AWS DMS operatsiyalari bir muncha vaqt harakatsiz. AWS DMS-dan foydalanganda behuda bitimlarni tugatmang.

PostgreSQL ma'lumotlar bazasini AWS DMS manbai sifatida ishlatishda xavfsizlik talablari

PostgreSQL-ni manba sifatida ishlatishda yagona xavfsizlik talabi - ko'rsatilgan foydalanuvchi hisobi PostgreSQL ma'lumotlar bazasida ro'yxatdan o'tgan foydalanuvchi bo'lishi kerak.



Limitations on Using a PostgreSQL Database as a Source for AWS DMS

The following limitations apply when using PostgreSQL as a source for AWS DMS:



  • A captured table must have a primary key. If a table doesn't have a primary key, AWS DMS ignores DELETE and UPDATE record operations for that table.

  • Timestamp with a time zone type column isn't supported.

  • AWS DMS ignores an attempt to update a primary key segment. In these cases, the target identifies the update as one that didn't update any rows. However, because the results of updating a primary key in PostgreSQL are unpredictable, no records are written to the exceptions table.

  • AWS DMS doesn't support the Start Process Changes from Timestamp run option.

  • AWS DMS supports full load and change processing on Amazon RDS for PostgreSQL. For information on how to prepare a PostgreSQL DB instance and to set it up for using CDC, see Setting Up an Amazon RDS PostgreSQL DB Instance as a Source.

  • Replication of multiple tables with the same name where each name has a different case (for example, table1, TABLE1, and Table1) can cause unpredictable behavior. Because of this issue, AWS DMS doesn't support this type of replication.

  • In most cases, AWS DMS supports change processing of CREATE, ALTER, and DROP DDL statements for tables. AWS DMS doesn't support this change processing if the tables are held in an inner function or procedure body block or in other nested constructs.

For example, the following change isn't captured:

CREATE OR REPLACE FUNCTION attu.create_distributors1() RETURNS void

LANGUAGE plpgsql

AS $$

BEGIN

create table attu.distributors1(did serial PRIMARY KEY,name

varchar(40) NOT NULL);

END;

$$;

  • AWS DMS doesn't support change processing of TRUNCATE operations.

  • The OID LOB data type isn't migrated to the target.

  • If your source is a PostgreSQL database that is on-premises or on an Amazon EC2 instance, ensure that the test_decoding output plugin is installed on your source endpoint. You can find this plugin in the Postgres contrib package. For more information about the test-decoding plugin, see the PostgreSQL documentation.

  • AWS DMS doesn’t support change processing to set column default values (using the ALTER COLUMN SET DEFAULT clause on ALTER TABLE statements).

  • AWS DMS doesn’t support change processing to set column nullability (using the ALTER COLUMN [SET|DROP] NOT NULL clause on ALTER TABLE statements).

  • AWS DMS doesn't support replication of partitioned tables. When a partitioned table is detected, the following occurs:

    • The endpoint reports a list of parent and child tables.

    • AWS DMS creates the table on the target as a regular table with the same properties as the selected tables.

    • If the parent table in the source database has the same primary key value as its child tables, a "duplicate key" error is generated.

Note

To replicate partitioned tables from a PostgreSQL source to a PostgreSQL target, you first need to manually create the parent and child tables on the target. Then you define a separate task to replicate to those tables. In such a case, you set the task configuration to Truncate before loading.



Note

The PostgreSQL NUMERIC data type isn't fixed in size. When transferring data that is a NUMERIC data type but without precision and scale, DMS uses NUMERIC(28,6) (a precision of 28 and scale of 6) by default. As an example, the value 0.611111104488373 from the source is converted to 0.611111 on the PostgreSQL target.



Setting Up an Amazon RDS PostgreSQL DB Instance as a Source

You can use an Amazon RDS for PostgreSQL DB instance or Read Replica as a source for AWS DMS. A DB instance can be used for both full-load and CDC (change data capture for ongoing replication); a Read Replica can only be used for full-load tasks and cannot be used for CDC.

You use the AWS master user account for the PostgreSQL DB instance as the user account for the PostgreSQL source endpoint for AWS DMS. The master user account has the required roles that allow it to set up change data capture (CDC). If you use an account other than the master user account, the account must have the rds_superuser role and the rds_replication role. The rds_replication role grants permissions to manage logical slots and to stream data using logical slots.

If you don't use the master user account for the DB instance, you must create several objects from the master user account for the account that you use. For information about creating the needed objects, see Migrating an Amazon RDS for PostgreSQL Database Without Using the Master User Account.



Using CDC with an RDS for PostgreSQL DB Instance

You can use PostgreSQL's native logical replication feature to enable CDC during a database migration of an Amazon RDS PostgreSQL DB instance. This approach reduces downtime and ensures that the target database is in sync with the source PostgreSQL database. Amazon RDS supports logical replication for a PostgreSQL DB instance version 9.4.9 and higher and 9.5.4 and higher.



Note

Amazon RDS for PostgreSQL Read Replicas cannot be used for CDC (ongoing replication).

To enable logical replication for an RDS PostgreSQL DB instance, do the following:


  • In general, use the AWS master user account for the PostgreSQL DB instance as the user account for the PostgreSQL source endpoint. The master user account has the required roles that allow it to set up CDC. If you use an account other than the master user account, you must create several objects from the master account for the account that you use. For more information, see Migrating an Amazon RDS for PostgreSQL Database Without Using the Master User Account.

  • Set the rds.logical_replication parameter in your DB parameter group to 1. This is a static parameter that requires a reboot of the DB instance for the parameter to take effect. As part of applying this parameter, AWS DMS sets the wal_level, max_wal_senders, max_replication_slots, and max_connections parameters. These parameter changes can increase Write Ahead Log (WAL) generation, so you should only set the rds.logical_replication parameter when you are using logical replication slots.

  • A best practice is to set the wal_sender_timeout parameter to 0. Setting this parameter to 0 prevents PostgreSQL from terminating replication connections that are inactive longer than the specified timeout. When AWS DMS is migrating data, replication connections need to be able to last longer than the specified timeout.

  • Enable native CDC start points with PostgreSQL as a source by setting the slotName extra connection attribute to the name of an existing logical replication slot when you create the endpoint. This logical replication slot holds ongoing changes from the time of endpoint creation. It thus supports replication from a previous point in time. PostgreSQL writes the database changes to WAL files that are discarded only after a client (like AWS DMS) successfully reads changes from the logical replication slot. Using logical replication slots can protect logged changes from being deleted before they are consumed by the replication engine. However, depending on rate of change and consumption, changes being held in a logical replication slot can cause elevated disk usage. We recommend that you set space usage alarms in the source PostgreSQL instance when logical replication slots are used. For more information on setting the slotName extra connection attribute, see Extra Connection Attributes When Using PostgreSQL as a Source for AWS DMS

Note

The use of native CDC start points with PostgresSQL as a source is available in AWS DMS versions 3.1.0 and later.



Migrating an Amazon RDS for PostgreSQL Database Without Using the Master User Account

In some cases, you might not use the master user account for the Amazon RDS PostgreSQL DB instance that you are using as a source. In these cases, you need to create several objects to capture data definition language (DDL) events. You create these objects in the account other than the master account and then create a trigger in the master user account.



Note

If you set the captureDDLs extra connection attribute to N on the source endpoint, you don't have to create the following table and trigger on the source database.



Use the following procedure to create these objects. The user account other than the master account is referred to as the NoPriv account in this procedure.

To create objects

  1. Choose the schema where the objects are to be created. The default schema is public. Ensure that the schema exists and is accessible by the NoPriv account.

  2. Log in to the PostgreSQL DB instance using the NoPriv account.

  3. Create the table awsdms_ddl_audit by running the following command, replacing objects_schema in the code following with the name of the schema to use.



  4. create table objects_schema.awsdms_ddl_audit

  5. (

  6. c_key bigserial primary key,

  7. c_time timestamp, -- Informational

  8. c_user varchar(64), -- Informational: current_user

  9. c_txn varchar(16), -- Informational: current transaction

  10. c_tag varchar(24), -- Either 'CREATE TABLE' or 'ALTER TABLE' or 'DROP TABLE'

  11. c_oid integer, -- For future use - TG_OBJECTID

  12. c_name varchar(64), -- For future use - TG_OBJECTNAME

  13. c_schema varchar(64), -- For future use - TG_SCHEMANAME. For now - holds current_schema

  14. c_ddlqry text -- The DDL query associated with the current DDL event

  15. )





  1. Create the function awsdms_intercept_ddl by running the following command, replacing objects_schema in the code following with the name of the schema to use.



  2. CREATE OR REPLACE FUNCTION objects_schema.awsdms_intercept_ddl()

  3. RETURNS event_trigger

  4. LANGUAGE plpgsql

  5. SECURITY DEFINER

  6. AS $$

  7. declare _qry text;

  8. BEGIN

  9. if (tg_tag='CREATE TABLE' or tg_tag='ALTER TABLE' or tg_tag='DROP TABLE') then

  10. SELECT current_query() into _qry;

  11. insert into objects_schema.awsdms_ddl_audit

  12. values

  13. (

  14. default,current_timestamp,current_user,cast(TXID_CURRENT()as varchar(16)),tg_tag,0,'',current_schema,_qry

  15. );

  16. delete from objects_schema.awsdms_ddl_audit;

  17. end if;

  18. END;

  19. $$;



  1. Log out of the NoPriv account and log in with an account that has the rds_superuser role assigned to it.

  2. Create the event trigger awsdms_intercept_ddl by running the following command.



  3. CREATE EVENT TRIGGER awsdms_intercept_ddl ON ddl_command_end

  4. EXECUTE PROCEDURE objects_schema.awsdms_intercept_ddl();

When you have completed the procedure preceding, you can create the AWS DMS source endpoint using the NoPriv account.



Removing AWS DMS Artifacts from a PostgreSQL Source Database

To capture DDL events, AWS DMS creates various artifacts in the PostgreSQL database when a migration task starts. When the task completes, you might want to remove these artifacts. To remove the artifacts, issue the following statements (in the order they appear), where {AmazonRDSMigration} is the schema in which the artifacts were created:



drop event trigger awsdms_intercept_ddl;

The event trigger doesn't belong to a specific schema.



drop function {AmazonRDSMigration}.awsdms_intercept_ddl()

drop table {AmazonRDSMigration}.awsdms_ddl_audit

drop schema {AmazonRDSMigration}

Note

Dropping a schema should be done with extreme caution, if at all. Never drop an operational schema, especially not a public one.



Additional Configuration Settings When Using a PostgreSQL Database as a Source for AWS DMS

You can add additional configuration settings when migrating data from a PostgreSQL database in two ways:



  • You can add values to the extra connection attribute to capture DDL events and to specify the schema in which the operational DDL database artifacts are created. For more information, see Extra Connection Attributes When Using PostgreSQL as a Source for AWS DMS.

  • You can override connection string parameters. Select this option if you need to do either of the following:

    • Specify internal AWS DMS parameters. Such parameters are rarely required and are therefore not exposed in the user interface.

    • Specify pass-through (passthru) values for the specific database client. AWS DMS includes pass-through parameters in the connection sting passed to the database client.

Using PostgreSQL Version 10.x as a Source for AWS DMS

PostgreSQL version 10.x databases have numerous changes in function names and folder names from previous PostgreSQL versions. These changes make certain migration actions not backward compatible when using AWS DMS versions earlier than 3.3.0.



Note

If you use a PostgreSQL 10.x database as a source in for AWS DMS 3.3.0 or later, don't perform the preparations described following. You can use the PostgreSQL source without any additional preparation.

Because most of the name changes are superficial, AWS DMS has created wrapper functions that let AWS DMS work with PostgreSQL versions 10.x. The wrapper functions are prioritized higher than functions in pg_catalog. In addition, we ensure that schema visibility of existing schemas isn't changed so that we don't override any other system catalog functions such as user-defined functions.

Ko'chib o'tish bo'yicha vazifalarni bajarishdan oldin ushbu o'rash funktsiyalaridan foydalanish uchun user_name, siz manbani so'nggi nuqtasini yaratishda foydalangan AWS DMS foydalanuvchi hisob qaydnomasidan ( ) foydalaning. Ushbu hisoblagich bilan ushbu funktsiyani aniqlash va bog'lash uchun, PostgreSQL manba ma'lumotlar bazasida quyidagi SQL kodini ishga tushiring.



BEGIN;

CREATE SCHEMA IF NOT EXISTS fnRenames;

CREATE OR REPLACE FUNCTION fnRenames.pg_switch_xlog() RETURNS pg_lsn AS $$

SELECT pg_switch_wal(); $$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION fnRenames.pg_xlog_replay_pause() RETURNS VOID AS $$

SELECT pg_wal_replay_pause(); $$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION fnRenames.pg_xlog_replay_resume() RETURNS VOID AS $$

SELECT pg_wal_replay_resume(); $$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION fnRenames.pg_current_xlog_location() RETURNS pg_lsn AS $$

SELECT pg_current_wal_lsn(); $$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION fnRenames.pg_is_xlog_replay_paused() RETURNS boolean AS $$

SELECT pg_is_wal_replay_paused(); $$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION fnRenames.pg_xlogfile_name(lsn pg_lsn) RETURNS TEXT AS $$

SELECT pg_walfile_name(lsn); $$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION fnRenames.pg_last_xlog_replay_location() RETURNS pg_lsn AS $$

SELECT pg_last_wal_replay_lsn(); $$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION fnRenames.pg_last_xlog_receive_location() RETURNS pg_lsn AS $$

SELECT pg_last_wal_receive_lsn(); $$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION fnRenames.pg_current_xlog_flush_location() RETURNS pg_lsn AS $$

SELECT pg_current_wal_flush_lsn(); $$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION fnRenames.pg_current_xlog_insert_location() RETURNS pg_lsn AS $$

SELECT pg_current_wal_insert_lsn(); $$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION fnRenames.pg_xlog_location_diff(lsn1 pg_lsn, lsn2 pg_lsn) RETURNS NUMERIC AS $$

SELECT pg_wal_lsn_diff(lsn1, lsn2); $$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION fnRenames.pg_xlogfile_name_offset(lsn pg_lsn, OUT TEXT, OUT INTEGER) AS $$

SELECT pg_walfile_name_offset(lsn); $$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION fnRenames.pg_create_logical_replication_slot(slot_name name, plugin name,

temporary BOOLEAN DEFAULT FALSE, OUT slot_name name, OUT xlog_position pg_lsn) RETURNS RECORD AS $$

SELECT slot_name::NAME, lsn::pg_lsn FROM pg_catalog.pg_create_logical_replication_slot(slot_name, plugin,

temporary); $$ LANGUAGE SQL;

ALTER USER user_name SET search_path = fnRenames, pg_catalog, "$user", public;
-- DROP SCHEMA fnRenames CASCADE;

-- ALTER USER PG_User SET search_path TO DEFAULT;

COMMIT;



Eslatma

Agar siz ushbu tayyorgarlik kodini PostgreSQL 10.x ma'lumotlar bazasida AWS DMS versiyalari uchun 3.3.0 dan oldinroq ishlamasangiz, siz quyidagi kabi xatoni ko'rasiz.



2018-10-29T02:57:50 [SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 42703 NativeError: 1 Message:

ERROR: column "xlog_position" does not exist;,

No query has been executed with that handle [1022502] (ar_odbc_stmt.c:3647)

AWS DMS-ni 3.3.0 yoki undan keyingi versiyasiga yangilaganingizdan so'ng, quyidagi amallarni bajaring:



  1. O'zingizning PostgreSQL 10.x konfiguratsiyani qidirish yo'lini belgilash uchun foydalanadigan fnRenamesma'lumotlardan ma'lumotnomani olib tashlang ALTER USER.

  2. fnRenamesPostgreSQL ma'lumotlar bazangizdan sxemani o'chirib tashlang .

Agar yangilangandan so'ng ushbu bosqichlarni bajarmasangiz, fnRenamessxemaga kirishda jurnaldagi quyidagi xatoni ko'rasiz :

RetCode: SQL_ERROR SqlState: 42703 NativeError: 1 Message: ERROR: column "lsn" does not exist;

Agar AWS DMS ma'lumotlar bazasi uchun usta bo'lmagan foydalanuvchi hisob qaydnomasidan foydalansa, shuningdek, manba PostgreSQL 10.x ma'lumotlar bazasi bilan ushbu o'rash funktsiyalariga kirish uchun ma'lum ruxsatlarni o'rnatishingiz kerak. Ushbu ruxsatlarni o'rnatish uchun quyidagi grantlarni bajaring:



GRANT USAGE ON SCHEMA fnRenames TO dms_superuser;

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA fnRenames TO dms_superuser;

For more information on using non-master user accounts with a source PostgreSQL 10.x database, see Migrating an Amazon RDS for PostgreSQL Database Without Using the Master User Account.



Extra Connection Attributes When Using PostgreSQL as a Source for AWS DMS

You can use extra connection attributes to configure your PostgreSQL source. You specify these settings when you create the source endpoint. If you have multiple connection attribute settings, separate them from each other by semicolons with no additional white space.

The following table shows the extra connection attributes that you can use when using PostgreSQL as a source for AWS DMS:


Name

Description

captureDDLs

To capture DDL events, AWS DMS creates various artifacts in the PostgreSQL database when the task starts. You can later remove these artifacts as described in Removing AWS DMS Artifacts from a PostgreSQL Source Database.

If this value is set to N, you don't have to create tables or triggers on the source database. For more information, see Migrating an Amazon RDS for PostgreSQL Database Without Using the Master User Account.

Streamed DDL events are captured.

Default value: Y

Valid values: Y/N

Example: captureDDLs=Y;



ddlArtifactsSchema

The schema in which the operational DDL database artifacts are created.

Default value: public

Valid values: String

Example: ddlArtifactsSchema=xyzddlschema;



failTasksOnLobTruncation

When set to true, this value causes a task to fail if the actual size of a LOB column is greater than the specified LobMaxSize.

If task is set to Limited LOB mode and this option is set to true, the task fails instead of truncating the LOB data.

Default value: false

Valid values: Boolean

Example: failTasksOnLobTruncation=true;


executeTimeout

Sets the client statement timeout for the PostgreSQL instance, in seconds. The default value is 60 seconds.

Example: executeTimeout=100;



slotName

Sets the name of a previously created logical replication slot for a CDC load of the source PostreSQL instance. When used with the CdcStartPosition request parameter, this also enables AWS DMS use of native CDC start points. AWS DMS verifies that the specified logical replication slot exists before starting the CDC load task. If the slot does not exist, AWS DMS raises an error.

For more information on how AWS DMS uses logical replication slots, see Using CDC with an RDS for PostgreSQL DB Instance. For more information on setting the CdcStartPosition request parameter, see the documentation about the CreateReplicationTask, StartReplicationTask, or ModifyReplicationTask APIs in the AWS Database Migration Service API Reference.

Valid values: String

Example: slotName=abc1d2efghijk_34567890_z0yx98w7_6v54_32ut_1srq_1a2b34c5d67ef;



Source Data Types for PostgreSQL

The following table shows the PostgreSQL source data types that are supported when using AWS DMS and the default mapping to AWS DMS data types.

For information on how to view the data type that is mapped in the target, see the section for the target endpoint you are using.

For additional information about AWS DMS data types, see Data Types for AWS Database Migration Service.



PostgreSQL Data Types

AWS DMS Data Types

INTEGER

INT4

SMALLINT

INT2

BIGINT

INT8

NUMERIC (p,s)

If precision is from 0 through 38, then use NUMERIC.

If precision is 39 or greater, then use STRING.



DECIMAL(P,S)

If precision is from 0 through 38, then use NUMERIC.

If precision is 39 or greater, then use STRING.



REAL

REAL4

DOUBLE

REAL8

SMALLSERIAL

INT2

SERIAL

INT4

BIGSERIAL

INT8

MONEY

NUMERIC(38,4)

MONEY ma'lumotlar turi SQL Serverda FLOAT-ga taqqoslanadi.



CHAR

WSTRING (1)

CHAR (N)

WSTRING (n)

VARCHAR (N)

WSTRING (n)

TEXT

NCLOB

BYTEA

BLOB

TIMESTAMP

TIMESTAMP

TIMESTAMP (z)

TIMESTAMP

Vaqt mintaqasi bilan TIMESTAMP

Qo'llab-quvvatlanmaydi

DATE

DATE

TIME

TIME

TIME (z)

TIME

INTERVAL

STRING (128) - 1 YIL, 2 oy, 3 kun, 4 soat, 5 daqiqa, 6 soniya

BOOLEAN

CHAR (5) noto'g'ri yoki haqiqiy

ENUM

STRING (64)

CIDR

STRING (50)

INET

STRING (50)

MAKADDR

STRING (18)

BIT (n)

STRING (n)

TURLI TURLI (n)

STRING (n)

UUID

STRING

TSVECTOR

CLOB

TSQUERY

CLOB

XML

CLOB

POINT

STRING (255) "(x, y)"

LINE

STRING (255) "(x, y, z)"

LSEG

STRING (255) "((x1, y1), (x2, y2))" "

BOX

STRING (255) "((x1, y1), (x2, y2))" "

PATH

CLOB "((x1, y1), (xn, yn))"

POLIGON

CLOB "((x1, y1), (xn, yn))"

QURILMA

STRING (255) "(x, y), r"

JSON

NCLOB

JSONB

NCLOB

ARRAY

NCLOB

Tarkibi

NCLOB

HSTORE

NCLOB

INT4RANGE

STRING (255)

INT8RANGE

STRING (255)

NUMRANGE

STRING (255)

Strang

STRING (255)

PostgreSQL ustun o'lchamlari PostgreSQL LOB ma'lumotlar turlarini AWS DMS ma'lumot turlariga o'zgartirishga ta'sir qiladi. Bu bilan ishlash uchun quyidagi AWS DMS ma'lumotlar turlari uchun quyidagi amallarni bajaring:

  • BLOB - sozlash uchun limiti LOB hajmi maksimal LOB hajmi (KB) vazifa yaratishga qiymati.

  • CLOB - Replikatsiya har bir belgi UTF8 belgisi sifatida ishlov beradi. Shuning uchun, bu erda ko'rsatilgan ustunda eng uzun belgilar belgilarining uzunligini toping va LOB hajmini cheklashmax_num_chars_text qiymatini aniqlash uchun foydalaning . Agar ma'lumotlar 4 baytli belgilarni o'z ichiga olsa, baytlarda bo'lgan LOB chegarasini belgilash uchun 2 ga ko'paytiring . Bunday holda, LOB hajminimax_num_chars_text 2 ga ko'paytirishga teng bo'ling .

  • NCLOB - Replikatsiya har bir belgi ikki baytli belgi sifatida ishlov beradi. Shuning uchun ( max_num_chars_text) ustunidagi eng uzun belgilar matnining uzunligini toping va LOB hajmini cheklash uchun 2 ga ko'paytiring . Bunday holda, LOB hajminimax_num_chars_text 2 ga ko'paytirish bilan cheklang . Agar ma'lumotlar 4 baytli belgilarni o'z ichiga olgan bo'lsa, ularni yana 2 ga ko'paytiring. Bunday holda, LOB hajminimax_num_chars_text 4 ga ko'paytirishga teng bo'ling .

Download 50.97 Kb.

Do'stlaringiz bilan baham:




Ma'lumotlar bazasi mualliflik huquqi bilan himoyalangan ©fayllar.org 2020
ma'muriyatiga murojaat qiling