perintah pgsql. Perintah dan skrip Postgres. Mendapatkan daftar tipe data yang tersedia

postgres=# BUAT DATABASE test_database; CREATE DATABASE postgres=# CREATE USER test_user DENGAN kata sandi "qwerty"; CREATE ROLE postgres=# HIBAH SEMUA hak istimewa PADA DATABASE test_database KE test_user; MENGANUGERAHKAN

Untuk keluar dari shell, masukkan perintah \q.

Sekarang mari kita coba bekerja dengan database yang dibuat atas nama test_user:

psql -h localhost test_database test_user

Mari kita buat tabel baru:

Test_database=> BUAT URUTAN user_id; CREATE SEQUENCE test_database=> CREATE TABLE users (id INTEGER PRIMARY KEY DEFAULT NEXTVAL("user_ids"), login CHAR(64), password CHAR(64)); PEMBERITAHUAN: CREATE TABLE / PRIMARY KEY akan BUAT INDEKS implisit "users_pkey" UNTUK TABEL "pengguna" CREATE TABLE

Menghapus pengguna

Untuk menghapus pengguna, Anda harus mentransfer haknya ke yang lain, dan kemudian hanya menghapus

TETAPKAN KEMBALI DIMILIKI OLEH doomed_role KE successor_role; DROP DIMILIKI OLEH doomed_role;

- ulangi perintah sebelumnya untuk setiap basis di cluster

JATUH PERAN doomed_role;

Skrip cadangan tabel Postgres.

#!/bin/bash DBNAMES="web"; PENGGUNA="postgres"; DB_NAME="web"; NEW_OWNER="pengguna_jarak jauh"; DATE_Y=`/bin/date "+%y"` DATE_M=`/bin/date "+%m"` DATE_D=`/bin/date "+%d"` SERVICE="pgdump" BACKUP_DIR="/var/ backup_db/20$(DATE_Y)/$(DATE_M)/$(DATE_D)" mkdir -p $BACKUP_DIR; untuk tbl di `psql -qAt -c "pilih nama tabel dari pg_tables di mana nama skema = "publik";" $(DB_NAME)` \ `psql -qAt -c "pilih sequence_name dari information_schema.sequences dimana sequence_schema = "public";" $(DB_NAME)` \ `psql -qAt -c "pilih table_name dari information_schema.views dimana table_schema = "public";" $(DB_NAME)` ; lakukan echo "Mengekspor tabel $tbl dari db $(DB_NAME) ke file tables3/$tbl.backup" #pg_dump --format p --verbose --table public.$tbl $(DB_NAME) > $BACKUP_DIR/$tbl pg_dump - -format p --verbose --table public.$tbl $(DB_NAME) | gzip > $BACKUP_DIR/$tbl #pg_dump -a -d -t public.$tbl $(DB_NAME) > tables3/$tbl.sql selesai #################BACKUP FUNGSI POSTGRES # Buang database tanpa tanggal, untuk mengekstraknya dari sana fungsi lebih lanjut pg_dump -Fc -s -f $BACKUP_DIR/db_dump $(DB_NAME) /bin/sleep 4; # Buat fungsi daftar pg_restore -l $BACKUP_DIR/db_dump | grep FUNCTION > $BACKUP_DIR/function_list ##Cara mengembalikan fungsi ######################## #pg_restore -h localhost -U username -d basename - L function_list db_dump ##############################

Skrip cadangan tabel Postgres. ditulis dalam perl

Jalankan dari bawah user'a - postgres. Jika di cron, maka juga dari bawah pengguna postgresql.

#!/usr/bin/env Perl gunakan ketat; gunakan peringatan; my $database_name = "buku_perpustakaan"; $kueri =<<"EOT"; SELECT n.nspname as table_schema, c.relname as table_name FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ("r","") AND n.nspname NOT IN ("pg_catalog", "information_schema") AND n.nspname NOT LIKE "^pg_%" ; EOT $query =~ s/\n\s*/ /g; my @results = `echo "$query" | psql -At $database_name`; foreach (@results) { chomp; my ($schema, $table) = split /\|/, $_; next unless ($schema && $table); my $cmd = "pg_dump -U postgres -Fp -t $schema.$table -f $schema.$table.dump $database_name"; system($cmd); } #If you wanted to restore only a single function: ##pg_restore -U $username --dbname=$dbname --function=$functionname(args) #If you wanted to restore only a single table: ##pg_restore -U $username --dbname=$dbname --table=$tablename

Kamus pencarian teks lengkap di postgresql

Cadangan basis data dari mesin jarak jauh

PGPASSWORD="PASSWORD" pg_dump -h $HOSTNAME -U databaseuser -Fc --verbose "database.itc-life.ru" | gzip > namadatabase.gz

Cadangkan dan pulihkan tabel

PADA PostgreSQL Ada dua utilitas untuk cadangan pg_dump dan pg_dumpall . pg_dump digunakan untuk mem-backup satu database, pg_dumpall digunakan untuk mem-backup semua database dan server secara keseluruhan (harus dijalankan di bawah superuser postgresql).

Membuat cadangan basis data mydb, terkompresi

Pg_dump -h localhost -p 5432 -U beberapa pengguna -F c -b -v -f mydb.backup mydb

Membuat cadangan basis data mydb, sebagai file teks biasa, termasuk perintah untuk membuat database

pg_dump -h localhost -p 5432 -U beberapa pengguna -C -F p -b -v -f mydb.backup mydb

Membuat cadangan basis data mydb, dalam bentuk terkompresi, dengan tabel yang berisi nama pembayaran

Pg_dump -h localhost -p 5432 -U beberapa pengguna -F c -b -v -t *pembayaran* -f payment_tables.backup mydb

Data dump hanya satu, tabel tertentu. Jika Anda perlu mencadangkan beberapa tabel, maka nama tabel ini dicantumkan menggunakan kunci -t untuk setiap meja.

pg_dump -a -t table_name -f file_name database_name

Buat cadangan terkompresi gz

pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c > mydb.gz

Daftar opsi yang paling umum digunakan:

H host - host, jika tidak ditentukan maka digunakan localhost PGHOST.

Port P - port, jika tidak ditentukan maka digunakan 5432 atau nilai dari variabel lingkungan PGPORT.

U - pengguna, jika tidak ditentukan maka pengguna saat ini digunakan, nilainya juga dapat ditentukan dalam variabel lingkungan PGUSER.

A, --data-only - hanya membuang data, data dan skema disimpan secara default.

B - sertakan objek besar (blog) di dump.

S, --schema-only - Buang skema saja.

C, --create - menambahkan perintah untuk membuat database.

C - menambahkan perintah untuk menghapus (menjatuhkan) objek (tabel, tampilan, dll.).

O - jangan menambahkan perintah untuk mengatur pemilik objek (tabel, tampilan, dll.).

F, --format (c|t|p) — membuang format keluaran, adat, ter, atau teks biasa.

T, --table=TABLE - tentukan tabel khusus untuk dump.

V, --verbose - keluaran informasi rinci.

D, --attribute-inserts - buang menggunakan perintah MENYISIPKAN dengan daftar nama properti.

Cadangkan semua database menggunakan perintah pg_dumpall.

pg_dumpall > all.sql # periksa cadangan grep "^[\]koneksi" all.sql \connect db1 \connect db2

PostgreSQL memiliki dua utilitas untuk pemulihan basis cadangan.

psql- mengembalikan cadangan yang disimpan dalam file teks biasa (plain text);
pg_restore— pemulihan cadangan terkompresi (tar);

Pemulihan basis data dan

#pg_restore -v -e -d dbname dbname.dump

Memulihkan seluruh cadangan dengan mengabaikan kesalahan

psql -h localhost -U beberapa pengguna -d dbname -f mydb.sql

Memulihkan seluruh cadangan, berhenti pada kesalahan pertama

psql -h localhost -U beberapa pengguna --set ON_ERROR_STOP=on -f mydb.sql

Untuk pulih dari ter-arsip kita harus terlebih dahulu membuat database menggunakan CREATE DATABASE mydb; (jika opsi -C tidak ditentukan saat membuat cadangan) dan pulihkan

pg_restore --dbname=mydb --jobs=4 --verbose mydb.backup

Memulihkan cadangan basis data terkompresi gz

Gunzip mydb.gz psql -U postgres -d mydb -f mydb

Mulai dari versi 9.2, hanya struktur tabel yang dapat dipulihkan menggunakan opsi --section

# membuat database BUAT DATABASE mydb2; # memulihkan pg_restore --dbname=mydb2 --section=pre-data --jobs=4 mydb.backup

Pemeliharaan Meja

tabel ANALISIS VAKUM; REINDEX DATABASE dbName; Nama tab REINDEX TABLE;

Memindahkan direktori data

Cari tahu jalan saat ini

# cara 1 $ su - postgres $ psql psql > SHOW data_directory; # cara 2 $ ps kapak | grep "postgres -D"

Buat direktori baru, tetapkan pengguna dan inisialisasi

mkdir -p /pathto/postgresql/data chown -R postgres:postgres /pathto/postgresql su - postgres initdb -D /pathto/postgresql/data

Sekarang kita perlu memperbaiki file dengan layanan yang dimulai postgresql

# di bawah arch linux sudo vim /etc/systemd/system/multi-user.target.wants/postgresql.service Environment =PGROOT=/pathto/postgresql/ PIDFile =/pathto/postgresql/data/postmaster.pid

Membersihkan meja

Membersihkan meja nama tabel dan reset counter dengan ID.

TRUNCATE TABLE tablename RESTART IDENTITY CASCADE ;

RIAM diperlukan dalam kasus nama tabel dihubungkan ke tabel lain.

Menghapus NULL dari bidang

ALTER TABLE film ALTER COLUMN tahun DROP NOT NULL ;

Menjalankan pgbouncer

su -s /bin/sh - postgres -c "/usr/sbin/pgbouncer -d --verbose /etc/pgbouncer/pgbouncer.ini"

Lepaskan pengguna dari database

PILIH pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = "mydb";`

Halo semuanya, hari ini saya ingin membuat pengingat kecil tentang perintah utama PostgreSQL. Anda dapat bekerja dengan PostgreSQL baik secara interaktif maupun dari baris perintah. Programnya adalah psql. Saya yakin daftar ini akan sangat berguna bagi Anda dan akan menghemat waktu Anda mencari melalui berbagai sumber. Biarkan saya mengingatkan Anda bahwa ini adalah proyek sumber terbuka berdasarkan Postgres DBMS, dirilis pada tahun 1986, sedang dikembangkan oleh grup pengembangan global PGDG, pada dasarnya adalah 5-8 orang, tetapi meskipun demikian, ini berkembang sangat secara intensif, memperkenalkan semua fungsi baru dan memperbaiki bug dan kesalahan lama.

Perintah dasar PostgreSQL dalam mode interaktif:

\connect db_name - sambungkan ke database bernama db_name
\du - daftar pengguna
\dp (atau \z) - daftar tabel, tampilan, urutan, hak aksesnya
\di - indeks
\ds - urutan
\dt - daftar tabel
\dt+ - daftar semua tabel dengan deskripsi
\dt *s* - daftar semua tabel yang mengandung s dalam namanya
\dv - tampilan
\dS - tabel sistem
\d+ - deskripsi tabel
\o - mengirim hasil kueri ke file
\l - daftar database
\i - membaca data yang masuk dari file
\e - membuka konten buffer kueri saat ini di editor (kecuali ditentukan lain dalam lingkungan variabel EDITOR, ini akan digunakan oleh vi secara default)
\d “nama_tabel” – deskripsi tabel
\i menjalankan perintah dari file eksternal, mis. \i /my/directory/my.sql
\pset - perintah untuk mengatur opsi pemformatan
\echo - menampilkan pesan
\set - Menetapkan nilai variabel lingkungan. Tanpa parameter, menampilkan daftar variabel saat ini (\unset - menghapus).
\? - referensi psql
\help - referensi SQL
\q (atau Ctrl+D) - keluar dari program

Bekerja dengan PostgreSQL dari baris perintah:

-c (atau --command) - menjalankan perintah SQL tanpa masuk ke mode interaktif
-f file.sql - menjalankan perintah dari file file.sql
-l (atau --list) daftar database yang tersedia
-U (atau --username) - tentukan nama pengguna (misalnya, postgres)
-W (atau --password) - permintaan kata sandi
-d dbname - terhubung ke database dbname
-h - nama host (server)
-s - mode langkah demi langkah, yaitu, Anda harus mengonfirmasi semua perintah
-S - mode satu baris, yaitu, baris baru akan mengeksekusi kueri (menghilangkan; di akhir konstruksi SQL)
-V - Versi PostgreSQL tanpa memasuki mode interaktif

Contoh:

psql -U postgres -d dbname -c "BUAT TABEL my(some_id serial PRIMARY KEY, some_text text);" - menjalankan perintah di database dbname.

psql -d dbname -H -c "SELECT * FROM my" -o my.html - keluaran hasil kueri ke file html.

Utilitas PostgreSQL (program):

createb dan dropdb - buat dan jatuhkan database (masing-masing)
createuser dan dropuser - buat dan pengguna (masing-masing)
pg_ctl adalah program yang dirancang untuk menyelesaikan tugas kontrol umum (mulai, hentikan, setel parameter, dll.)
postmaster - modul server multi-pengguna PostgreSQL (konfigurasi level debug, port, direktori data)
initdb - membuat cluster PostgreSQL baru
initlocation - program untuk membuat direktori untuk penyimpanan sekunder database
vacuumdb - pemeliharaan fisik dan analitik database
pg_dump - pengarsipan dan pemulihan data
pg_dumpall - buat cadangan seluruh cluster PostgreSQL
pg_restore - memulihkan basis data dari arsip (.tar, .tar.gz)

Contoh cadangan:

Membuat cadangan database mydb, dalam bentuk terkompresi

Pg_dump -h localhost -p 5440 -U beberapa pengguna -F c -b -v -f mydb.backup mydb

Membuat cadangan database mydb, sebagai file teks biasa, termasuk perintah untuk membuat database

pg_dump -h localhost -p 5432 -U beberapa pengguna -C -F p -b -v -f mydb.backup mydb

Membuat cadangan database mydb, dalam bentuk terkompresi, dengan tabel yang berisi pembayaran atas nama

Pg_dump -h localhost -p 5432 -U beberapa pengguna -F c -b -v -t *pembayaran* -f payment_tables.backup mydb

Data dump hanya satu, tabel tertentu. Jika lebih dari satu tabel perlu dicadangkan, maka nama tabel dicantumkan menggunakan opsi -t untuk setiap tabel.

pg_dump -a -t table_name -f file_name database_name

Buat cadangan dengan kompresi gz

Pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c > mydb.gz

Daftar opsi yang paling umum digunakan:

-h host - host, jika tidak ditentukan maka localhost atau nilai dari variabel lingkungan PGHOST digunakan.
-p port - port, jika tidak ditentukan maka 5432 atau nilai dari variabel lingkungan PGPORT digunakan.
-u - pengguna, jika tidak ditentukan maka pengguna saat ini digunakan, nilainya juga dapat ditentukan dalam variabel lingkungan PGUSER.
-a, -data-only - hanya membuang data, menyimpan data dan skema secara default.
-b - sertakan objek besar (blog) di dump.
-s, -schema-only - hanya membuang skema.
-C, -create - menambahkan perintah untuk membuat database.
-c - menambahkan perintah untuk menghapus (menjatuhkan) objek (tabel, tampilan, dll.).
-O Jangan menambahkan perintah untuk mengatur pemilik objek (tabel, tampilan, dll.).
-F, -format (c|t|p) - membuang format keluaran, kustom, tar, atau teks biasa.
-t, -table=TABLE - Tentukan tabel tertentu untuk dibuang.
-v, -verbose - keluaran verbose.
-D, -attribute-inserts Dump menggunakan perintah INSERT dengan daftar nama properti.

Cadangkan semua database menggunakan perintah pg_dumpall.

pg_dumpall > all.sql

Memulihkan tabel dari cadangan (backup):

psql - memulihkan cadangan yang disimpan dalam file teks biasa (teks biasa);
pg_restore - pulihkan cadangan terkompresi (tar);

Memulihkan seluruh cadangan dengan mengabaikan kesalahan

psql -h localhost -U beberapa pengguna -d dbname -f mydb.sql

Memulihkan seluruh cadangan, berhenti pada kesalahan pertama

psql -h localhost -U someuser -set ON_ERROR_STOP=on -f mydb.sql

Untuk memulihkan dari tarball, pertama-tama kita perlu membuat database dengan CREATE DATABASE mydb; (jika opsi -C tidak ditentukan saat membuat cadangan) dan pulihkan

pg_restore -dbname=mydb -jobs=4 -verbose mydb.backup

Memulihkan cadangan basis data yang dikompresi dengan gz

psql -U postgres -d mydb -f mydb

Saya pikir database postgresql sekarang akan lebih dimengerti oleh Anda. Saya harap daftar perintah PostgreSQL ini bermanfaat bagi Anda.

Pada artikel ini, saya akan menunjukkan 15 perintah yang paling berguna untuk mengelola postgreSQL.

1. Bagaimana cara mengubah kata sandi root di PostgreSQL?

$ /usr/local/pgsql/bin/psql postgres postgres Kata sandi: (kata sandi lama) # ALTER USER postgres DENGAN PASSWORD 'tmppassword'; $ /usr/local/pgsql/bin/psql postgres postgres Kata Sandi: (tmppassword)

Mengubah kata sandi untuk pengguna biasa bekerja dengan cara yang sama. Pengguna root dapat mengubah kata sandi untuk setiap pengguna.

# ALTER username PENGGUNA DENGAN PASSWORD 'tmppassword';

2. Bagaimana cara mengatur PostgreSQL ke autostart?

$ su - root # tar xvfz postgresql-8.3.7.tar.gz # cd postgresql-8.3.7 # cp contrib/start-scripts/linux /etc/rc.d/init.d/postgresql # chmod a+x / etc/rc.d/init.d/postgresql

3. Periksa status server

$ /etc/init.d/postgresql status Kata sandi: pg_ctl: server sedang berjalan (PID: 6171) /usr/local/pgsql/bin/postgres "-D" "/usr/local/pgsql/data" [ Komentar: Pesan ini menunjukkan bahwa server aktif dan berjalan dengan baik] $ /etc/init.d/postgresql status Kata sandi: pg_ctl: tidak ada server yang berjalan [ Komentar: Pesan ini menunjukkan bahwa server tidak berjalan]

4. Bagaimana cara memulai, menghentikan, memulai ulang PostgreSQL?

# layanan postgresql berhenti Menghentikan PostgreSQL: server berhenti ok # layanan postgresql mulai Memulai PostgreSQL: ok # layanan postgresql restart Restart PostgreSQL: server berhenti ok

5. Bagaimana cara melihat versi PostgreSQL yang sedang berjalan?

$ /usr/local/pgsql/bin/psql tes Selamat datang di psql 8.3.7, terminal interaktif PostgreSQL. Ketik: \hak cipta untuk istilah distribusi \h untuk bantuan dengan perintah SQL \? untuk bantuan dengan perintah psql \g atau akhiri dengan titik koma untuk menjalankan kueri \q untuk keluar dari tes=# pilihanversi(); versi ————————————————————————————————- PostgreSQL 8.3.7 pada i686-pc-linux-gnu, dikompilasi oleh GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42) (1 baris) tes=#

5. Bagaimana cara membuat pengguna di PostgreSQL?

Ada dua cara untuk ini..

Metode 1: Kami membuat pengguna di shell PSQL menggunakan perintah CREATE USER.

# BUAT USER ramesh DENGAN kata sandi 'tmppassword'; BUAT PERAN

metode2: Kami membuat pengguna melalui perintah shell createuser.

$ /usr/local/pgsql/bin/createuser sathiya Haruskah peran baru menjadi pengguna super? (y/n) Apakah peran baru diizinkan untuk membuat database? (y/n) Haruskah peran baru diizinkan untuk membuat lebih banyak peran baru? (y/n) n BUAT PERAN

6. Bagaimana cara membuat database di PostgreSQL?

Ada 2 metode untuk ini.

metode1: Buat database menggunakan shell PSQL menggunakan perintah CREATE DATABASE.

# BUAT DATABASE mydb DENGAN PEMILIK ramesh; BUAT DATABASE

metode2: Kami menggunakan perintah createb.

$ /usr/local/pgsql/bin/createdb mydb -O ramesh BUAT DATABASE

7. Mendapatkan daftar semua database di Postgresql?

8. Bagaimana cara menghapus database di PostgreSQL?

9. Menggunakan bantuan bawaan untuk perintah

Tim \? akan menampilkan baris bantuan untuk perintah psql. \h CREATE akan menampilkan bantuan untuk semua perintah yang dimulai dengan CREATE.

# \? # \h BUAT # \h BUAT INDEKS

10. Bagaimana cara mendapatkan daftar semua tabel dalam database di Postgresql?

# \d

Untuk database kosong, Anda akan mendapatkan pesan “Tidak ada hubungan yang ditemukan.”

11. Bagaimana saya bisa mengetahui waktu eksekusi permintaan?

# \timing - setelah menjalankan perintah ini, setiap permintaan berikutnya akan menunjukkan waktu eksekusi.

# \waktu Waktu aktif. # PILIH * dari pg_catalog.pg_attribute ; Waktu: 9.583ms

12. Bagaimana cara mencadangkan dan memulihkan database dan tabel di PostgreSQL?

Pertanyaan ini cukup besar dan saya akan mempublikasikannya nanti di artikel terpisah.

13. Bagaimana cara membuat daftar fungsi yang tersedia di PostgreSQL?

Untuk daftar fungsi yang tersedia, ucapkan \df+

# \df # \df+

14. Bagaimana cara mengedit kueri PostgreSQL di editor?

# \e

\e akan membuka editor tempat Anda dapat mengedit kueri dan menyimpannya.

15. Di mana saya dapat menemukan file riwayat postgreSQL?

Seperti file ~/.bash_history, postgreSQL menyimpan semua perintah sql di file ~/.psql_history.

$ cat ~/.psql_history ubah postgres pengguna dengan kata sandi 'tmppassword'; \h mengubah versi pilihan pengguna(); buat ramesh pengguna dengan kata sandi 'tmppassword'; \timing pilih * dari pg_catalog.pg_attribute;

Pembaruan terakhir: 17/03/2018

Tabel dibuat menggunakan perintah CREATE TABLE diikuti dengan nama tabel. Anda juga dapat menggunakan sejumlah operator dengan perintah ini yang mendefinisikan kolom tabel dan atributnya. Sintaks umum untuk membuat tabel adalah sebagai berikut:

CREATE TABLE table_name (column_name1 data_type column_attributes1, column_name2 data_type column2 atribut, ..................................... .... .......... column_nameN data_type column_attributesN, table_attributes);

Setelah nama tabel, spesifikasi untuk semua kolom dicantumkan dalam tanda kurung. Selain itu, untuk setiap kolom, Anda harus menentukan nama dan tipe data yang akan diwakilinya. Tipe data menentukan data apa (angka, string, dll.) yang dapat berisi kolom.

Sebagai contoh, mari kita buat tabel di database menggunakan pgAdmin. Untuk melakukan ini, pertama-tama pilih database target di pgAdmin, klik kanan padanya dan pilih item Alat Kueri... di menu konteks:

Setelah itu, kolom untuk memasukkan kode dalam SQL akan terbuka. Selain itu, tabel akan dibuat khusus untuk database di mana kita akan membuka bidang ini untuk memasukkan SQL.

BUAT pelanggan TABEL (Id SERIAL PRIMARY KEY, VARIASI KARAKTER Nama Depan(30), VARIASI KARAKTER Nama Belakang(30), VARIASI KARAKTER Email(30), Umur INTEGER);

Dalam hal ini, lima kolom ditentukan dalam tabel Pelanggan: Id, FirstName, LastName, Age, Email. Kolom pertama, Id, mewakili ID pelanggan, berfungsi sebagai kunci utama dan oleh karena itu bertipe SERIAL . Bahkan, kolom ini akan menyimpan nilai numerik 1, 2, 3, dst., yang secara otomatis akan bertambah satu untuk setiap baris baru.

Tiga kolom berikutnya mewakili nama depan, nama belakang, dan alamat email pelanggan dan bertipe CHARACTER VARYING(30) , yang berarti mewakili string tidak lebih dari 30 karakter.

Kolom terakhir - Usia mewakili usia pengguna dan bertipe INTEGER , yaitu menyimpan angka.

Dan setelah menjalankan perintah ini, tabel pelanggan akan ditambahkan ke database yang dipilih.

Menghapus tabel

Untuk menjatuhkan tabel, gunakan perintah DROP TABLE, yang memiliki sintaks berikut:

DROP TABLE table1 [, table2, ...];

Misalnya, menghapus tabel pelanggan.

15 Perintah PostgreSQL yang Berguna

Ada banyak manual PostgreSQL di internet yang menjelaskan perintah dasar. Tetapi ketika menyelam lebih dalam ke pekerjaan, ada masalah praktis yang memerlukan perintah lanjutan.

Perintah seperti itu, atau cuplikan, jarang didokumentasikan. Mari kita lihat beberapa contoh yang berguna bagi pengembang dan administrator basis data.

Mendapatkan informasi tentang database

Ukuran basis data

Untuk mendapatkan ukuran fisik file (penyimpanan) database, gunakan kueri berikut:

PILIH pg_database_size(current_database());

Hasilnya akan disajikan sebagai angka seperti 41809016 .

current_database() adalah fungsi yang mengembalikan nama database saat ini. Sebagai gantinya, Anda dapat memasukkan nama dalam teks:

PILIH pg_database_size("my_database");

Untuk mendapatkan informasi dalam bentuk yang dapat dibaca manusia, kami menggunakan fungsi pg_size_pretty:

PILIH pg_size_pretty(pg_database_size(current_database()));

Hasilnya, kami mendapatkan informasi seperti 40 Mb .

Daftar tabel

Terkadang Anda perlu mendapatkan daftar tabel database. Untuk melakukan ini, kami menggunakan kueri berikut:

SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN ("information_schema","pg_catalog");

information_schema - skema database standar yang berisi kumpulan tampilan (views), seperti tabel, bidang, dll. Tampilan tabel berisi informasi tentang semua tabel database.

Kueri yang dijelaskan di bawah ini akan memilih semua tabel dari skema tertentu dari database saat ini:

SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN ("information_schema", "pg_catalog") AND table_schema IN("public", "myschema");

Dalam klausa IN terakhir, Anda dapat menentukan nama skema tertentu.

Ukuran meja

Dengan analogi dengan mendapatkan ukuran database, ukuran data tabel dapat dihitung menggunakan fungsi yang sesuai:

SELECT pg_relation_size("akun");

Fungsi pg_relation_size mengembalikan ukuran yang ditempati oleh lapisan tertentu dari tabel atau indeks yang diberikan pada disk.

Nama meja terbesar

Untuk menampilkan daftar tabel dalam database saat ini, diurutkan menurut ukuran tabel, jalankan kueri berikut:

PILIH relname, relpages DARI pg_class ORDER BY relpages DESC;

Untuk menampilkan informasi tentang tabel terbesar, kami membatasi kueri menggunakan LIMIT:

PILIH relname, relpages DARI pg_class ORDER BY relpages DESC LIMIT 1;

relname adalah nama tabel, indeks, tampilan, dll.
relpages - ukuran representasi tabel ini pada disk dalam jumlah halaman (secara default, satu halaman adalah 8 KB).
pg_class adalah tabel sistem yang berisi informasi tentang hubungan tabel database.

Daftar pengguna yang terhubung

Untuk mengetahui nama, IP, dan port yang digunakan dari pengguna yang terhubung, jalankan kueri berikut:

PILIH datname,usename,client_addr,client_port DARI pg_stat_activity;

Aktivitas pengguna

Untuk mengetahui aktivitas koneksi pengguna tertentu, gunakan kueri berikut:

PILIH datname FROM pg_stat_activity WHERE usename = "devuser";

Bekerja dengan bidang data dan tabel

Menghapus garis duplikat

Jika kebetulan tabel tersebut tidak memiliki kunci utama (primary key), maka pasti akan terjadi duplikasi di antara record-record tersebut. Jika untuk tabel seperti itu, terutama yang besar, perlu untuk mengatur batasan (constraint) untuk memeriksa integritas, lalu hapus elemen berikut:

garis duplikat
situasi di mana satu atau lebih kolom diduplikasi (jika kolom ini seharusnya digunakan sebagai kunci utama).

Pertimbangkan tabel dengan data pelanggan, di mana seluruh baris diduplikasi (yang kedua berturut-turut).

Kueri berikut akan membantu menghapus semua duplikat:

HAPUS DARI pelanggan WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customers.*);

Bidang ctid, yang unik untuk setiap catatan, disembunyikan secara default, tetapi ada di setiap tabel.

Permintaan terakhir adalah sumber daya yang intensif, jadi berhati-hatilah saat menjalankannya pada proyek yang sedang berjalan.

Sekarang pertimbangkan kasus ketika nilai bidang diulang.

Jika menghapus duplikat tanpa menyimpan semua data dapat diterima, jalankan kueri berikut:

HAPUS DARI pelanggan WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customer_id);

Jika datanya penting, maka Anda harus terlebih dahulu menemukan catatan dengan duplikat:

SELECT * FROM customers WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customer_id);

Sebelum menghapus catatan tersebut, Anda dapat memindahkannya ke tabel sementara atau mengganti nilai customer_id di dalamnya dengan yang lain.

Bentuk umum permintaan untuk menghapus catatan yang dijelaskan di atas adalah sebagai berikut:

DELETE FROM table_name WHERE ctid NOT IN (SELECT max(ctid) FROM table_name GROUP BY column1, );

Perubahan jenis bidang yang aman

Mungkin ada pertanyaan tentang memasukkan tugas seperti itu dalam daftar ini. Memang, di PostgreSQL, mengubah jenis bidang sangat sederhana menggunakan perintah ALTER. Mari kita lihat tabel pelanggan lagi sebagai contoh.

Bidang customer_id menggunakan tipe data string varchar. Ini adalah kesalahan karena bidang ini seharusnya menyimpan ID pelanggan, yang dalam format bilangan bulat. Menggunakan varchar tidak dibenarkan. Mari kita coba untuk memperbaiki kesalahpahaman ini menggunakan perintah ALTER:

ALTER TABLE pelanggan ALTER COLUMN customer_id TYPE integer;

Tetapi sebagai hasil eksekusi kami mendapatkan kesalahan:

KESALAHAN: kolom "customer_id" tidak dapat dilemparkan secara otomatis untuk mengetik bilangan bulat
Status SQL: 42804
Petunjuk: Tentukan ekspresi MENGGUNAKAN untuk melakukan konversi.

Artinya, Anda tidak bisa begitu saja mengambil dan mengubah tipe bidang ketika ada data di tabel. Karena tipe varchar digunakan, DBMS tidak dapat menentukan apakah nilainya adalah integer . Meskipun data sesuai dengan jenis ini. Untuk memperjelas poin ini, pesan kesalahan menyarankan menggunakan klausa USING untuk mengonversi data kita menjadi bilangan bulat dengan benar:

ALTER TABLE pelanggan ALTER COLUMN customer_id TYPE integer MENGGUNAKAN (customer_id::integer);

Hasilnya, semuanya berjalan tanpa kesalahan:

Harap dicatat bahwa saat menggunakan MENGGUNAKAN, selain ekspresi tertentu, dimungkinkan untuk menggunakan fungsi, bidang dan operator lain.

Misalnya, mari kita ubah bidang customer_id kembali ke varchar , tetapi dengan konversi format data:

ALTER TABLE pelanggan ALTER COLUMN customer_id TYPE varchar MENGGUNAKAN (customer_id || "-" || first_name);

Hasilnya, tabel akan terlihat seperti ini:

Cari nilai "hilang"

Hati-hati saat menggunakan urutan sebagai kunci utama: saat menetapkan, beberapa elemen urutan secara tidak sengaja dilewati, sebagai akibat dari bekerja dengan tabel, beberapa catatan dihapus. Nilai seperti itu dapat digunakan lagi, tetapi sulit ditemukan di tabel besar.

Pertimbangkan dua opsi pencarian.

Cara pertama
Mari kita jalankan kueri berikut untuk menemukan awal interval dengan nilai "hilang":

SELECT customer_id + 1 FROM customers mo MANA TIDAK ADA (SELECT NULL FROM customers mi WHERE mi.customer_id = mo.customer_id + 1) ORDER BY customer_id;

Hasilnya, kami mendapatkan nilai: 5 , 9 dan 11 .

Jika Anda perlu menemukan tidak hanya kemunculan pertama, tetapi semua nilai yang hilang, gunakan kueri (padat sumber daya!) berikut:

DENGAN seq_max AS (SELECT max(customer_id) FROM customers), seq_min AS (SELECT min(customer_id) FROM customers) SELECT * FROM generate_series((SELECT min FROM seq_min),(SELECT max FROM seq_max)) KECUALI SELECT customer_id FROM pelanggan;

Hasilnya, kita melihat hasil berikut: 5 , 9 dan 6 .

Cara kedua
Dapatkan nama urutan yang terkait dengan customer_id:

SELECT pg_get_serial_sequence("pelanggan", "id_pelanggan");

Dan temukan semua pengidentifikasi yang hilang:

DENGAN sequence_info AS (SELECT start_value, last_value FROM "SchemaName"."SequenceName") SELECT generate_series ((sequence_info.start_value), (sequence_info.last_value)) FROM sequence_info KECUALI PILIH customer_id FROM pelanggan;

Menghitung jumlah baris dalam sebuah tabel

Jumlah baris dihitung dengan fungsi hitungan standar, tetapi dapat digunakan dengan kondisi tambahan.

Jumlah total baris dalam tabel:

SELECT count(*) FROM tabel;

Jumlah baris asalkan bidang yang ditentukan tidak berisi NULL:

SELECT count(col_name) DARI tabel;

Jumlah baris unik untuk bidang yang ditentukan:

SELECT count(col_name berbeda) DARI tabel;

Menggunakan transaksi

Transaksi menggabungkan urutan tindakan menjadi satu operasi. Keunikannya adalah jika terjadi kesalahan dalam pelaksanaan transaksi, tidak ada hasil tindakan yang akan disimpan dalam database.

Mari kita mulai transaksi dengan perintah BEGIN.

Untuk memutar kembali semua operasi setelah BEGIN , gunakan perintah ROLLBACK.

Dan untuk menerapkan - perintah COMMIT.

Melihat dan Mengakhiri Kueri yang Dapat Dieksekusi

Untuk mendapatkan informasi tentang permintaan, jalankan perintah berikut:

PILIH pid, umur(query_start, clock_timestamp()), usename, query FROM pg_stat_activity WHERE query != " " DAN kueri TIDAK SEPERTI "%pg_stat_activity%" ORDER BY query_start desc;

Untuk menghentikan permintaan tertentu, jalankan perintah berikut, dengan menentukan id proses (pid):

PILIH pg_cancel_backend(procpid);

Untuk menghentikan permintaan, jalankan:

PILIH pg_terminate_backend(procpid);

Bekerja dengan konfigurasi

Menemukan dan Mengubah Lokasi Instance Cluster

Situasi mungkin terjadi ketika beberapa instance PostgreSQL dikonfigurasi pada sistem operasi yang sama, yang "duduk" di port yang berbeda. Dalam hal ini, menemukan jalur ke lokasi fisik setiap instance adalah tugas yang cukup menegangkan. Untuk mendapatkan informasi ini, jalankan kueri berikut terhadap database cluster yang diminati:

TAMPILKAN data_directory;

Ubah lokasi ke lokasi lain menggunakan perintah:

SET data_directory ke new_directory_path;

Tetapi reboot diperlukan agar perubahan diterapkan.

Mendapatkan daftar tipe data yang tersedia

Dapatkan daftar tipe data yang tersedia menggunakan perintah:

PILIH nama ketik, ketik dari pg_type di mana typetype="b";

typname adalah nama tipe data.
typlen - ukuran tipe data.

Mengubah pengaturan DBMS tanpa me-reboot

Pengaturan PostgreSQL terletak di file khusus seperti postgresql.conf dan pg_hba.conf . Setelah mengubah file-file ini, Anda memerlukan DBMS untuk mendapatkan pengaturan lagi. Untuk melakukan ini, server database dimulai ulang. Jelas bahwa Anda harus melakukan ini, tetapi pada versi produksi proyek, yang digunakan oleh ribuan pengguna, ini sangat tidak diinginkan. Oleh karena itu, PostgreSQL memiliki fungsi yang dapat digunakan untuk menerapkan perubahan tanpa memulai ulang server:

PILIH pg_reload_conf();

Tapi, sayangnya, itu tidak berlaku untuk semua parameter. Dalam beberapa kasus, reboot diperlukan untuk menerapkan pengaturan.

Kami telah melihat perintah yang akan mempermudah pengembang dan DBA menggunakan PostgreSQL. Tapi ini tidak semua metode yang mungkin. Jika Anda menemukan tugas yang menarik, tulis di komentar. Mari berbagi pengalaman bermanfaat kami!