search

yugabyte / yugabyte-db

YugabyteDB - the cloud native distributed SQL database for mission-critical applications.
https://www.yugabyte.com
Other
9.04k stars 1.08k forks source link

[Backups] Cannot load data from ysql_dump #10281

Open tsmull-11 opened 3 years ago

tsmull-11 commented 3 years ago

Jira Link: DB-875 I created a free tier cloud cluster and have been trying to load the data from a different cluster. The total uncompressed sstable size is 1GB, so this should be relatively straightforward. The issue seems to be with the way the data is dumped out via this process. I have narrowed it down to the transactions table, From the file we can see that transactions prints out like so using copy:

COPY public.transactions (id, amount, message, pictures, likes, "privacyType", complete, hidden, "pendingReceiverEmail", "pendingReceiverPhoneNumber", "createdAt", "updatedAt", "senderId", "receiverId") FROM stdin;
b12dca40-7da7-11ea-b426-e9a509afd313    0.00000100  Get extra 20M for using my code BestenderGames  {}  1   public  t   f   \N  \N  2020-04-13 16:56:17.38+00   2020-04-13 17:08:47.552+00  G59qrLKiVXNt0nqbY1JeK8FMnfG3    afoJFQy9fXgyR0GYDZO669CYLNu1
762f8e50-a7ec-11ea-81a2-bd0061fb8445    0.00000700  Hola gente 👋🏻 voy a estar siguiendo a todo aquel que me siga🤜🏻🤛🏻\n100%real😎📱📲\n🎁Mi código: aliescalante {}  1   public  t   f   \N  \N  2020-06-06 11:54:22.641944+00   2020-06-06 11:54:22.641944+00   6LL4VGrmM7TTFqBo9AEjCJkzTNA2    YDDPBfTxaSXLVm1nV2EAVE5ofNn1

The issue lies with trying to restore from the file where we get the following errors:

ysqlsh:/Users/ericpratt/Desktop/publicschema.sql:728098: invalid command \N
ysqlsh:/Users/ericpratt/Desktop/publicschema.sql:728099: invalid command \nHave
ysqlsh:/Users/ericpratt/Desktop/publicschema.sql:728100: invalid command \nJust
ysqlsh:/Users/ericpratt/Desktop/publicschema.sql:1011304: ERROR:  syntax error at or near "36"
LINE 1: 36efeb50-f142-11ea-add6-039ed4bb22a1 0.00700000 🎁 your free ...

If I adjust the ysql_dump to use inserts then we can get the data loaded, but it takes up to 8 hours for only 1GB. This will become a problem for production

m-iancu commented 3 years ago

@tsmull-11 Can we confirm if the source and target cluster had the same locale, encoding, as well as same extensions installed (i.e. the new cluster has all the extensions this schema might use).