N
Neon•2y ago
flat-fuchsia

Permission error using `pgcopydb clone --follow`

What I get a permission error when I try to run pgcopydb clone --follow to run a continuous migration from my source db in Amazon to my target db in Neon. It looks like the pgcopydb tool cannot create the necessary replication objects on the Neon side using the sales_owner user that I set up in my Neon project. Here is the relevant part of the error message (I will reply with rest of the error): 
permission denied for function pg_replication_origin_oid
permission denied for function pg_replication_origin_oid
How can I set up my Neon instance so that I can run pgcopydb with the --follow option? Why I want to perform as seamless a migration as I can from my current production database to my new production database in Neon. pgcopydb clone works fine if I want to dump and restore and stop there. But I'd like to do a dump, restore and continuously run a replication until I do the switch-over. How First I followed the instructions to migrate from Aurora to Neon using DMS here: https://neon.tech/docs/import/migrate-aws-dms I tried to run the migration using DMS but it failed to work AND I was unable to set up the permissions properly so that the migration service could log out the error statements to debug the issue. After a day of fiddling with it and getting nowhere, I switched to use pgcopydb. pgcopydb is a tool which uses pg_dump and pg_restore to migrate data from one Postgres server to another in an efficient way. One of the things that pgcopydb allows you to do is to clone and then "follow" changes which replicates changes from source to target until you terminate the process. This is appealing because it makes it possible for us to get 99% of the migration done up front and then perform the cut-over when we are ready. I was hoping to do this with DMS but I couldn't get it working. I am able to copy the data using pgcopydb clone. But as soon as I add the --follow argument I get the error that I mentioned above.
Neon
Migrate with AWS Database Migration Service (DMS) - Neon Docs
This guide outlines the steps for using the AWS Database Migration Service (DMS) to migrate data to Neon from another hosted database server. AWS DMS supports a variety of database migration sources i...
9 Replies
flat-fuchsia
flat-fuchsiaOP•2y ago
Here's the full error output: 
INFO   main.c:136                Running pgcopydb version 0.15.74.gc74047a from "/usr/local/bin/pgcopydb"
INFO   cli_common.c:1385         [SOURCE] Copying database from "postgres://root@0.0.0.0:6432/sales?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60"
INFO   cli_common.c:1386         [TARGET] Copying database into "postgres://sales_owner@*****.ap-southeast-2.aws.neon.tech:5432/sales?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60"
INFO   copydb.c:105              Using work dir "/tmp/pgcopydb"
INFO   pgsql.c:4133              Created logical replication slot "pgcopydb" with plugin "test_decoding" at 4/D0F493F0 and exported snapshot 00000009-0015ED82-1
ERROR  pgsql.c:2352              [TARGET 1586791274] ERROR:  permission denied for function pg_replication_origin_oid
ERROR  pgsql.c:2352              [TARGET 1586791274] ERROR:  permission denied for function pg_replication_origin_oid
ERROR  pgsql.c:2363              [TARGET 1586791274] SQL query: select pg_replication_origin_oid($1)
ERROR  pgsql.c:2371              [TARGET 1586791274] SQL params: 'pgcopydb'
ERROR  pgsql.c:5026              Failed to get replication origin oid for "pgcopydb"
INFO   main.c:136                Running pgcopydb version 0.15.74.gc74047a from "/usr/local/bin/pgcopydb"
INFO   cli_common.c:1385         [SOURCE] Copying database from "postgres://root@0.0.0.0:6432/sales?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60"
INFO   cli_common.c:1386         [TARGET] Copying database into "postgres://sales_owner@*****.ap-southeast-2.aws.neon.tech:5432/sales?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60"
INFO   copydb.c:105              Using work dir "/tmp/pgcopydb"
INFO   pgsql.c:4133              Created logical replication slot "pgcopydb" with plugin "test_decoding" at 4/D0F493F0 and exported snapshot 00000009-0015ED82-1
ERROR  pgsql.c:2352              [TARGET 1586791274] ERROR:  permission denied for function pg_replication_origin_oid
ERROR  pgsql.c:2352              [TARGET 1586791274] ERROR:  permission denied for function pg_replication_origin_oid
ERROR  pgsql.c:2363              [TARGET 1586791274] SQL query: select pg_replication_origin_oid($1)
ERROR  pgsql.c:2371              [TARGET 1586791274] SQL params: 'pgcopydb'
ERROR  pgsql.c:5026              Failed to get replication origin oid for "pgcopydb"
sensitive-blue
sensitive-blue•2y ago
Hey! Logical replication is not currently available. We plan on shipping an update soon. Right now, the only way to migrate your data is to use pg_dump directly.
sensitive-blue
sensitive-blue•2y ago
cc @Daniel might be worth documenting pgcopydb once we support logical replication https://github.com/dimitri/pgcopydb
GitHub
GitHub - dimitri/pgcopydb: Copy a Postgres database to a target Pos...
Copy a Postgres database to a target Postgres server (pg_dump | pg_restore on steroids) - dimitri/pgcopydb
optimistic-gold
optimistic-gold•2y ago
This would be a nice addition. I've put it on our task board.
flat-fuchsia
flat-fuchsiaOP•2y ago
Hey @Mahmoud, thanks for letting me know. I thought that might be the case. Good to get confirmation! Cheers
genetic-orange
genetic-orange•2y ago
Hey @Cody, While logical replication gets rolled out, if you want to migrate and test out neon with pg_dump and pg_restore, try out using the @neondatabase/migrate-pg package ⬇️ https://github.com/neondatabase/migrate-pg Would love to hear any kind of feedback if you end up trying it.
flat-fuchsia
flat-fuchsiaOP•2y ago
A follow-up question: given what @Mahmoud said:
Logical replication is not currently available
That sounds like I won't be able to use DMS for the same purpose either, correct? Copy + replicate?
sensitive-blue
sensitive-blue•2y ago
yea pg_dump is the only way to import data into Neon for now unless AWS DMS provides a way that doesn't use logical replication
optimistic-gold
optimistic-gold•2y ago
You don't require logical replication for AWS DMS. We provide some instructions here, but those instructions are based on a sample dataset that was not being actively modified: https://neon.tech/docs/import/migrate-aws-dms
Neon
Migrate with AWS Database Migration Service (DMS) - Neon Docs
This guide outlines the steps for using the AWS Database Migration Service (DMS) to migrate data to Neon from another hosted database server. AWS DMS supports a variety of database migration sources i...

Did you find this page helpful?