Menghapus semua data dalam basis data Postgres

Saya telah membuat dump db baru dari server produksi dengan flag --data-only dan --column-inserts, jadi saya hanya memiliki sekumpulan pernyataan sisipan untuk menyisipkan data saat melakukan restore pada server staging.

pg_dump -h localhost -U adminuser --data-only --column-inserts maindb > maindb.sql

Bagaimana cara menghapus semua data di database server staging terlebih dahulu, sebelum memulihkan data dari dump produksi?

Saya hanya ingin menghapus semua data saja, jadi saya tidak perlu drop dan membuat database dan sebagainya. Saya hanya ingin menghapus data dan menyisipkan data baru itu saja.

Saya tidak memiliki opsi untuk menghapus dan membuat database karena beberapa alasan. Saya harus menghapus semua data dan hanya menyisipkan saja, jadi apa pun yang diperlukan untuk menemukan cara melakukan ini, saya bersedia melakukannya tetapi jelas membutuhkan bantuan untuk memulai.

Saya juga perlu mengotomatisasi proses ini. Akan mengotomatisasi "membuang data dari db produksi &", lalu "menghapus data pada staging db &", dan kemudian "mengembalikan data ke staging db &". Saya hanya perlu bantuan pada bagian "menghapus data pada staging db &".

Saya berjalan di PostgreSQL 9.5.2

Paul White says GoFundMonica
Pertanyaan edit 3 November 2016 в 4:27
Administrator Basis Data
postgresql postgresql-9.5 pg-dump
2 November 2016 в 4:06
Belum ada tampilan
Larutan

Anda tidak harus menghapus database, cukup dengan menghapus semua objek dalam database. Ini bisa dilakukan dengan menggunakan

drop owned by adminuser

Jika Anda kemudian membuat SQL dump termasuk pernyataan create table (jadi tanpa opsi --data-only) semuanya akan baik-baik saja.

Anda juga dapat menghapus --column-inserts, yang akan membuat impor menjadi lebih cepat.

Namun, jika Anda ingin menghapus semuanya, Anda dapat melakukannya dengan sedikit SQL dinamis:

do
$$
declare
  l_stmt text;
begin
  select 'truncate ' || string_agg(format('%I.%I', schemaname, tablename), ',')
    into l_stmt
  from pg_tables
  where schemaname in ('public');

  execute l_stmt;
end;
$$

Ini akan memotong semua tabel dalam skema publik dengan satu pernyataan yang juga akan bekerja bahkan jika ada banyak kendala kunci asing yang menghubungkan semua tabel. Jika tabel-tabel Anda tersebar di beberapa skema, Anda perlu menambahkannya dalam kondisi where.

Komentar (3)

pg_restore memiliki flag --clean (atau mungkin --create) yang akan menghapus data secara otomatis sebelum menjalankan operasi ...

The Excellent Documentation akan sangat membantu Anda...

Hanya untuk memperjelas, jika hal ini membingungkan:

Bersihkan (drop) database objek sebelum membuatnya kembali. (Kecuali jika --if-exists digunakan, ini mungkin akan menghasilkan beberapa pesan kesalahan yang tidak berbahaya, jika ada objek yang tidak ada di database tujuan).

Ini tidak akan menghilangkan basis data yang sebenarnya ... hanya tabel/tampilan/dll.

Jika, untuk beberapa alasan, menjatuhkan dan membuat ulang tabel tidak dapat diterima, maka Anda harus bekerja lebih keras untuk membuat skrip secara manual yang membuat dump hanya data dari db sumber, mengeluarkan TRUNCATE atau DELETE di database target, dan kemudian memuat dump data. Tidak ada cara cepat / licin untuk melakukan ini, sejauh yang saya ketahui.

Joishi Bodio
Jawaban edit 2 November 2016 в 5:55
Komentar (4)
 
SELECT 'TRUNCATE ' || input_table_name || ' CASCADE;' AS truncate_query FROM(SELECT table_schema || '.' || table_name AS input_table_name FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema') AND table_schema NOT LIKE 'pg_toast%') AS information;

Kueri di atas akan menghasilkan kueri terpotong untuk semua tabel dalam database.

Komentar (0)