PostgreSQL Migration을 진행해 보자!

Dump Database

• 터미널이나 DataGrip 콘솔에서 아래 명령어 실행

pg_dump -h old_host -U old_user -d old_database_name -F c -f /path/to/backup_file.dump

Parameters:

• h: The host of the PostgreSQL server (omit if it's local).
• U: The username for the PostgreSQL server.
• d: The name of the old database.
• F c: Specifies the format as a custom archive (which is more flexible for restoration).
• f: The output file to save the backup (/path/to/backup_file.dump).

Restore Database

pg_restore -h new_host -U new_user -d new_database_name -F c /path/to/backup_file.dump

Parameters:

• h: Host of the PostgreSQL server (omit if it's local).
• U: Username for the PostgreSQL server.
• d: Name of the new database where the backup will be restored.
• F c: Format of the dump file as a custom archive.
• /path/to/backup_file.dump: The path to the dump file created earlier.

ERROR 발생

...
pg_restore: from TOC entry 251; 1259 24856 TABLE test_table your_role
pg_restore: error: could not execute query: ERROR:  role "your_role" does not exist
Command was: ALTER TABLE public.test_table OWNER TO your_role;

pg_restore: warning: errors ignored on restore: 45

pg_restore --no-owner Option 사용

pg_restore -h new_host -U new_user -d new_database_name -F c --no-owner /path/to/backup_file.dump

Ownership 변경

• psql Command line 접속

psql -h new_host -U user_name -d database_name

• Ownership 변경

-- For tables
DO $$ DECLARE
  r RECORD;
BEGIN
  FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
    EXECUTE 'ALTER TABLE ' || quote_ident(r.tablename) || ' OWNER TO your_role;';
  END LOOP;
END $$;

-- For sequences
DO $$ DECLARE
  r RECORD;
BEGIN
  FOR r IN (SELECT sequencename FROM pg_sequences WHERE schemaname = 'public') LOOP
    EXECUTE 'ALTER SEQUENCE ' || quote_ident(r.sequencename) || ' OWNER TO your_role;';
  END LOOP;
END $$;

-- For functions
DO $$ DECLARE
  r RECORD;
BEGIN
  FOR r IN (SELECT routine_name FROM information_schema.routines WHERE routine_schema = 'public') LOOP
    EXECUTE 'ALTER FUNCTION ' || quote_ident(r.routine_name) || '() OWNER TO your_role;';
  END LOOP;
END $$;

• 소유권 확인

-- table
SELECT tablename, tableowner FROM pg_tables WHERE schemaname = 'public';
-- sequence
SELECT sequence_name, owner FROM information_schema.sequences WHERE sequence_schema = 'public';
--function
SELECT routine_name, specific_schema, routine_type, specific_name, routine_definition, external_language
FROM information_schema.routines
WHERE specific_schema = 'public';

ERROR 발생

SELECT sequence_name, owner FROM information_schema.sequences WHERE sequence_schema = 'public';
ERROR:  column "owner" does not exist
LINE 1: SELECT sequence_name, owner FROM information_schema.sequence...

The error you're seeing happens because the column owner does not exist in the information_schema.sequences view in PostgreSQL. PostgreSQL does not store the owner directly in the information_schema.sequences table. Instead, you need to retrieve the owner information from the pg_class and pg_roles system catalogs.

• 쿼리 수정

SELECT seq.relname AS sequence_name, pg_roles.rolname AS owner
FROM pg_class seq
JOIN pg_namespace ns ON seq.relnamespace = ns.oid
JOIN pg_roles ON seq.relowner = pg_roles.oid
WHERE seq.relkind = 'S' AND ns.nspname = 'public';