SQLITE SQL 덤프 파일을 POSTGRESQL로 변환
저는 POSTGRESQL에서 프로덕션과 함께 SQLITE 데이터베이스를 사용하여 개발을 해왔습니다. 방대한 양의 데이터로 로컬 데이터베이스를 업데이트했으며 특정 테이블을 프로덕션 데이터베이스로 전송해야합니다.
실행을 기반으로 sqlite database .dump > /the/path/to/sqlite-dumpfile.sql
SQLITE는 다음 형식으로 테이블 덤프를 출력합니다.
BEGIN TRANSACTION;
CREATE TABLE "courses_school" ("id" integer PRIMARY KEY, "department_count" integer NOT NULL DEFAULT 0, "the_id" integer UNIQUE, "school_name" varchar(150), "slug" varchar(50));
INSERT INTO "courses_school" VALUES(1,168,213,'TEST Name A',NULL);
INSERT INTO "courses_school" VALUES(2,0,656,'TEST Name B',NULL);
....
COMMIT;
위를 프로덕션 서버로 가져올 수있는 POSTGRESQL 호환 덤프 파일로 어떻게 변환합니까?
해당 덤프 파일을 다음 위치에 직접 공급할 수 있어야합니다 psql
.
/path/to/psql -d database -U username -W < /the/path/to/sqlite-dumpfile.sql
당신이 원하는 경우 id
"자동 증가"에 열을 후 테이블 생성 줄에 "시리얼"에서 "INT"에서 유형을 변경합니다. 그런 다음 PostgreSQL은 해당 열에 시퀀스를 연결하여 NULL ID가있는 INSERT에 다음 사용 가능한 값을 자동으로 할당합니다. PostgreSQL은 AUTOINCREMENT
명령을 인식하지 못 하므로 제거해야합니다.
또한 datetime
SQLite 스키마에서 열 을 확인하고 timestamp
PostgreSQL 용으로 변경하고 싶을 것입니다 (이를 지적한 Clay 에게 감사드립니다 ).
SQLite에 부울이있는 경우 1
및 0
및 1::boolean
및 0::boolean
(각각)를 변환 하거나 덤프의 스키마 섹션에서 부울 열을 정수로 변경 한 다음 가져 오기 후 PostgreSQL 내에서 직접 수정할 수 있습니다.
SQLite에 BLOB가있는 경우를 사용하도록 스키마를 조정하고 싶을 것 bytea
입니다. 일부 decode
통화도 믹스해야 할 것입니다 . 하지만 처리해야 할 BLOB가 많으면 좋아하는 언어로 빠르고 더러운 복사기를 작성하는 것이 SQL을 조작하는 것보다 쉬울 수 있습니다.
평소처럼 외래 키가있는 경우 set constraints all deferred
삽입 순서 문제를 방지하기 위해 BEGIN / COMMIT 쌍 안에 명령을 배치하는 것이 좋습니다.
부울, 블롭 및 제약 사항에 대해 Nicolas Riley 에게 감사드립니다 .
`
일부 SQLite3 클라이언트에 의해 생성 된 코드 가있는 경우 이를 제거해야합니다.
PostGRESQL은 또한 unsigned
열을 인식하지 못하므로 이를 삭제하거나 다음과 같은 사용자 정의 제약 조건을 추가 할 수 있습니다.
CREATE TABLE tablename (
...
unsigned_column_name integer CHECK (unsigned_column_name > 0)
);
SQLite는 기본적으로 null 값 ''
을로 설정하지만 PostgreSQL에서는이를로 설정해야합니다 NULL
.
SQLite 덤프 파일의 구문은 대부분 PostgreSQL과 호환되는 것으로 보이므로 몇 가지 사항을 패치하고 psql
. SQL INSERT를 통해 많은 양의 데이터를 가져 오는 데는 시간이 걸릴 수 있지만 작동합니다.
pgloader
SQLite 덤프를 PostgreSQL로 변환하는 방법을 찾을 때이 게시물을 보았습니다. 이 게시물에 허용 된 답변 (그리고 해당 +1에서 좋은 답변)이 있지만 추가하는 것이 중요하다고 생각합니다.
여기서 솔루션을 찾기 시작했고 더 자동화 된 방법을 찾고 있다는 것을 깨달았습니다. 위키 문서를 찾았습니다.
https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL
그리고 발견 pgloader
. 매우 멋진 응용 프로그램이며 비교적 사용하기 쉽습니다. 플랫 SQLite 파일을 사용 가능한 PostgreSQL 데이터베이스로 변환 할 수 있습니다. 에서 설치 하고 테스트 디렉토리에 다음과 같은 파일을 *.deb
만들었습니다 command
.
load database
from 'db.sqlite3'
into postgresql:///testdb
with include drop, create tables, create indexes, reset sequences
set work_mem to '16MB', maintenance_work_mem to '512 MB';
문서 상태 처럼 . 그런 다음 다음 testdb
과 createdb
같이 만들었습니다 .
createdb testdb
다음 pgloader
과 같은 명령을 실행했습니다 .
pgloader command
그런 다음 새 데이터베이스에 연결됩니다.
psql testdb
데이터를 확인하기 위해 몇 가지 쿼리를 수행 한 후 꽤 잘 작동하는 것으로 보입니다. 이 스크립트 중 하나를 실행하거나 여기에 언급 된 단계적 변환을 시도했다면 훨씬 더 많은 시간을 보냈을 것입니다.
개념을 증명하기 위해 나는 이것을 버리고 testdb
프로덕션 서버의 개발 환경으로 가져오고 데이터를 멋지게 전송했습니다.
내가 할 수있는 스크립트 작성 sqlite3
에 postgres
마이그레이션. https://stackoverflow.com/a/4581921/1303625에 언급 된 모든 스키마 / 데이터 번역을 처리하지는 않지만 필요한 작업을 수행합니다. 다른 사람들에게 좋은 출발점이되기를 바랍니다.
https://gist.github.com/2253099
The sequel gem (a Ruby library) offers data copying across different databases: http://sequel.jeremyevans.net/rdoc/files/doc/bin_sequel_rdoc.html#label-Copy+Databases
In case of sqlite, it would be like this: sequel -C sqlite://db/production.sqlite3 postgres://user@localhost/db
You can use a one liner, here is an example with the help of sed command:
sqlite3 mjsqlite.db .dump | sed -e 's/INTEGER PRIMARY KEY AUTOINCREMENT/SERIAL PRIMARY KEY/' | sed -e 's/PRAGMA foreign_keys=OFF;//' | sed -e 's/unsigned big int/BIGINT/g' | sed -e 's/UNSIGNED BIG INT/BIGINT/g' | sed -e 's/BIG INT/BIGINT/g' | sed -e 's/UNSIGNED INT(10)/BIGINT/' | sed -e 's/BOOLEAN/SMALLINT/g' | sed -e 's/boolean/SMALLINT/g' | sed -e 's/UNSIGNED BIG INT/INTEGER/g' | sed -e 's/INT(3)/INT2/g' | sed -e 's/DATETIME/TIMESTAMP/g' | psql mypqdb mypguser
I have tried editing/regexping the sqlite dump so PostgreSQL accepts it, it is tedious and prone to error.
What I got to work really fast:
First recreate the schema on PostgreSQL without any data, either editing the dump or if you were using an ORM you may be lucky and it talks to both back-ends (sqlalchemy, peewee, ...).
Then migrate the data using pandas. Suppose you have a table with a bool field (which is 0/1 in sqlite, but must be t/f in PostgreSQL)
def int_to_strbool(df, column):
df = df.replace({column: 0}, 'f')
df = df.replace({column: 1}, 't')
return df
#def other_transform(df, column):
#...
conn = sqlite3.connect(db)
df = pd.read_sql(f'select * from {table_name}', conn)
df = int_to_strbool(df, bool_column_name)
#df = other_transform(df, other_column_name)
df.to_csv(table_name + '.csv'), sep=',', header=False, index=False)
This works like a charm, is easy to write, read and debug each function, unlike (for me) the regular expressions.
Now you can try to load the resulting csv with PostgreSQL (even graphically with the admin tool), with the only caveat that you must load the tables with foreign keys after you have loaded the tables with the corresponding source keys. I did not have the case of a circular dependency, I guess you can suspend temporarily the key checking if that is the case.
pgloader work wonders on converting database in sqlite to postgresql.
Here's an example on converting a local sqlitedb to a remote PostgreSQL db:
pgloader sqlite.db postgresql://username:password@hostname/dbname
참고URL : https://stackoverflow.com/questions/4581727/convert-sqlite-sql-dump-file-to-postgresql
'program tip' 카테고리의 다른 글
문자열이 null인지 비어 있는지 확인하는 가장 쉬운 방법 (0) | 2020.09.16 |
---|---|
Apache HttpClient를 사용하여 JSON 요청을 게시하는 방법은 무엇입니까? (0) | 2020.09.16 |
예외 :“URI 형식은 지원되지 않습니다.” (0) | 2020.09.16 |
LocalDate를 Instant로 변환하는 방법? (0) | 2020.09.16 |
.Net에서 PDF 문서 읽기 (0) | 2020.09.16 |