0

I'm definitely a beginner at database management, but am trying my best to figure this out without being too needy. I have two PostgreSQL databases, and am trying to migrate data from one to the other. I used pg_dump to dump my database into a .dump file:

pg_dump -Fc -U my_user -h my_host > mydb.dump

Then, I attempted to load in my data into another PostgreSQL instance on AWS RDS:

pg_restore -U my_other_user -h my_new_host --clean --create --no-owner mydb.dump

However, I don't see any of my data populating at all in my new database. I do see the output from the terminal:

ALTER TABLE ONLY public.one_table
    ADD CONSTRAINT one_table_fkey FOREIGN KEY (column) REFERENCES public.another_table(id);

--
-- Name: TABLE my_table; Type: ACL; Schema: public; Owner: -
--

GRANT SELECT ON TABLE public.my_table TO "read-only";
GRANT SELECT ON SEQUENCE public.my_table_seq TO "read-only";

--
-- Name: DEFAULT PRIVILEGES FOR SEQUENCES; Type: DEFAULT ACL; Schema: -; Owner: -
--

ALTER DEFAULT PRIVILEGES FOR ROLE my_user GRANT SELECT ON SEQUENCES TO "read-only";

--
-- Name: DEFAULT PRIVILEGES FOR TABLES; Type: DEFAULT ACL; Schema: -; Owner: -
--

ALTER DEFAULT PRIVILEGES FOR ROLE my_user GRANT SELECT ON TABLES  TO "read-only";

--
-- PostgreSQL database dump complete
--

I suspect there's some issues with ownership? Do my old to new database names need to match? I checked to see if a new DB popped up in my new RDS instance with my old DB name, no luck.

I've tried adding a new database and restoring there:

pg_restore -U my_other_user -h my_new_host --clean --create --no-owner -d new_db mydb.dump

but after it restores, I cannot even see this new database within Postico (my GUI client).

I know this isn't too much information to go off of, but please do let me know what else I need to provide to help figure out what is going. I can definitely try restoring table by table, but I'd prefer using pg_dump and pg_restore since I've heard it's much more efficient and less work (once I get it to work!)

Yu Chen
  • 6,540
  • 6
  • 51
  • 86
  • When using --create pg_restore should make a new database with same name as the one you backed up, even when you specify -d. If you want to restore into the database specified by -d then remove the --create. Ownership and privileges from the backup are not a problem, pg_restore will report an error if it cannot set them but it will continue loading the data in the backup. Did you refresh/restart your gui client after restoring the database? – Eelke Jul 06 '18 at 04:38
  • @Eelke thanks... I think I'm on the right track. I set the command to be `pg_restore -U my_other_user -h my_new_host --clean --no-owner -d new_db --verbose mydb.dump` and am seeing `pg_restore: processing data for table ...` which I take to be a good sign. I actually think this might have been my issue: https://stackoverflow.com/questions/5900888/why-is-pg-restore-returning-successfully-but-not-actually-restoring-my-database. I saw a ton of output to my stdout, but likely because I didn't specify a valid db. Aargh. – Yu Chen Jul 06 '18 at 05:24
  • Indeed I didn't realize at first but when you want to directly load into the database you always must specify -d with a valid database to which it can connect inittialy. When you have specified --create it will then use that connection to create the database with the name from the archive and then switch to that database. If no -d is present it will just output the sql queries to stdout. – Eelke Jul 06 '18 at 13:18

0 Answers0