psqlを使用してコマンドラインからPostgreSQLデータベースを管理する

今こそ、リレーショナルデータベースとSQLを学ぶ絶好の機会です。Web開発からデータサイエンスまで、どこでも使用されています。

Stack Overflow 2021調査では、プロの開発者が使用した上位5つのデータベーステクノロジのうち4つは、リレーショナルデータベース管理システムでした。  

PostgreSQLは、最初に学習するリレーショナルデータベース管理システムとして優れた選択肢です。

1. Uber、Netflix、Instagram、Spotify、Twitchなどの業界で広く使用されています。
2. オープンソースなので、特定のベンダーに縛られることはありません。
3. 25年以上の歴史があり、その間、安定性と信頼性で定評があります。

freeCodeCampリレーショナルデータベース認定から学習している場合でも、自分のコンピューターでPostgreSQLを試している場合でも、データベースを作成および管理し、データベースにデータを挿入し、データベースからデータをクエリする方法が必要です。

PostgreSQLと対話するためのグラフィカルアプリケーションはいくつかありますが、psqlとコマンドラインを使用することが、データベースと通信するための最も直接的な方法である可能性があります。

psqlとは何ですか？

psqlは、ターミナルインターフェイスを介してPostgreSQLデータベースと対話できるようにするツールです。PostgreSQLをマシンにインストールすると、psqlが自動的に含まれます。

psqlを使用すると、SQLクエリを記述し、それらをPostgreSQLに送信して、結果を表示できます。また、データベースを管理するためのメタコマンド（円記号で始まる）を使用することもできます。スクリプトを記述して、データベースに関連するタスクを自動化することもできます。

さて、ローカルコンピュータでデータベースを実行し、コマンドラインを使用することは、最初は恐ろしいように思えるかもしれません。私はそれが本当にそれほど悪くないことをあなたに言うためにここにいます。このガイドでは、データベースの作成、管理、バックアップ、復元の方法など、コマンドラインからPostgreSQLデータベースを管理するための基本について説明します。

前提条件–PostgreSQLをインストールする

コンピュータにPostgreSQLをまだインストールしていない場合は、PostgreSQLの公式ドキュメントに記載されているオペレーティングシステムの手順に従ってください。

PostgreSQLをインストールすると、パスワードの入力を求められます。作成するデータベースに接続するために必要になるため、これは安全な場所に保管してください。

データベースに接続する方法

psqlを使用してデータベースに接続する場合、2つのオプションがあります。コマンドラインまたはpsqlアプリケーションを使用して接続できます。どちらもほぼ同じエクスペリエンスを提供します。

オプション1-コマンドラインでデータベースに接続する

ターミナルを開きます。と入力すると、psqlがインストールされていることを確認できますpsql --version。が表示されます。これは、マシンにインストールされているPostgreSQLのバージョンですpsql (PostgreSQL) version_number。version_number私の場合は14.1です。

コマンドラインからpsqlのバージョンを確認する

データベースに接続するためのパターンは次のとおりです。

psql -d database_name -U username

-dフラグはの代わりになり--dbnameます-Uが、の代わりになり--usernameます。

PostgreSQLをインストールすると、デフォルトのデータベースとユーザーが作成され、どちらも。と呼ばれpostgresます。したがって、Enterキーを押して、スーパーユーザーとしてデータベースpsql -d postgres -U postgresに接続します。postgrespostgres

psql -d postgres -U postgres

パスワードの入力を求められます。コンピューターにPostgreSQLをインストールしたときに選択したパスワードを入力します。ターミナルプロンプトが変わり、postgresデータベースに接続していることを示します。

psqlを使用してコマンドラインからデータベースに接続する

（スーパーユーザーとしてではなくpostgres）自分自身としてデータベースに直接接続する場合は、ユーザー名の値としてシステムのユーザー名を入力します。

オプション2–psqlアプリケーションを使用してデータベースに接続します

psqlアプリケーションを起動します–「SQLシェル（psql）」と呼ばれます。サーバー、データベース、ポート、およびユーザー名の入力を求められます。localhostEnterキーを押すだけでpostgres、デフォルト値（、、、、、および）を選択でき5432ますpostgres。

次に、PostgreSQLのインストール時に選択したパスワードの入力を求められます。これを入力すると、ターミナルプロンプトが変わり、postgresデータベースに接続していることが示されます。

psqlアプリケーションを使用してデータベースに接続する

注： Windowsを使用している場合は、「コンソールコードページ（850）はWindowsコードページ（1252）とは異なります。8ビット文字が正しく機能しない可能性があります。詳細については、psqlリファレンスページの「Windowsユーザー向けの注意事項」を参照してください。」この段階では、これについて心配する必要はありません。詳細については、psqlのドキュメントを参照してください。

psqlでヘルプを取得する方法

すべてのpsqlメタコマンドのリストと、それらの機能の概要を表示するには、\?コマンドを使用します。

\?

psqlのヘルプコマンド

PostgreSQLコマンドのヘルプが必要な場合は、またはとコマンドを使用し\hて\helpください。

\h COMMAND

これにより、コマンドの説明、その構文（角括弧内のオプション部分を含む）、およびPostgreSQLドキュメントの関連部分のURLが提供されます。

DROPTABLEステートメントを説明するpsql

psqlでコマンドを終了する方法

時間がかかるコマンドを実行したり、コンソールに大量の情報を出力したりした場合は、。と入力してコマンドを終了できますq。

q

データベースの作成方法

データベースを管理する前に、データベースを作成する必要があります。

注： SQLコマンドはセミコロンで終了する必要がありますが、メタコマンド（円記号で始まる）はセミコロンで終了する必要はありません。

データベースを作成するためのSQLコマンドは次のとおりです。

CREATE DATABASE database_name;

このガイドでは、本のデータを処理するので、というデータベースを作成しましょうbooks_db。

CREATE DATABASE books_db;

データベースを一覧表示する方法

listコマンドを使用して、使用可能なすべてのデータベースのリストを表示できます。

\l

すべてのデータベースの一覧表示

、、、、およびが表示books_dbされます。（このコマンドは、実際にはと呼ばれる標準データベースをコピーすることで機能します。これについて詳しくは、PostgreSQLのドキュメントを参照してください。）postgrestemplate0template1CREATE DATABASEtemplate1

を使用\l+すると、データベースのサイズやそのテーブルスペース（データベースを表すファイルが保存されるファイルシステム内の場所）などの追加情報が表示されます。

\l+

追加情報を含むすべてのデータベースの一覧表示

データベースを切り替える方法

現在、デフォルトのpostgresデータベースに接続しています。データベースに接続したり、データベースを切り替えたりするには、\cコマンドを使用します。

\c database_name

だから\c books_dbあなたをbooks_dbデータベースに接続します。現在接続しているデータベースを反映して、ターミナルプロンプトが変化することに注意してください。

データベースの切り替え

データベースを削除する方法

データベースを削除する場合は、DROP DATABASEコマンドを使用します。

DROP DATABASE database_name;

postgresなどのスーパーユーザーである場合、またはデータベースの所有者である場合にのみ、データベースを削除できます。

存在しないデータベースを削除しようとすると、エラーが発生します。IF EXISTS代わりに通知を受け取るために使用します。

DROP DATABASE IF EXISTS database_name;

データベースの削除

アクティブな接続があるデータベースを削除することはできません。したがって、現在接続しているデータベースを削除する場合は、別のデータベースに切り替える必要があります。

テーブルの作成方法

テーブルを管理する前に、いくつかのテーブルを作成し、それらにサンプルデータを入力する必要があります。

テーブルを作成するコマンドは次のとおりです。

CREATE TABLE table_name();

これにより、空のテーブルが作成されます。列の値を括弧に渡して、列のあるテーブルを作成することもできます。少なくとも、基本テーブルには、主キー（各行を区別するための一意の識別子）と、データを含む列が必要です。

私たちbooks_dbのために、著者用のテーブルと本用のテーブルを作成します。著者の場合、名前と名前を記録します。書籍については、タイトルと出版年を記録します。

著者first_nameとlast_name本titleがnullでないことを確認します。これは、著者について知るための非常に重要な情報だからです。これを行うために、NOT NULL制約を含めます。

CREATE TABLE authors(
	author_id SERIAL PRIMARY KEY, 
	first_name VARCHAR(100) NOT NULL, 
	last_name VARCHAR(100) NOT NULL
);

CREATE TABLE books(
	book_id SERIAL PRIMARY KEY, 
	title VARCHAR(100) NOT NULL, 
	published_year INT
);

CREATE TABLEテーブルが正常に作成された場合は、端末に印刷されたことが表示されます。

次に、本に外部キーを追加して、2つのテーブルを接続しましょう。外部キーは、別のテーブルの主キーを参照する一意の識別子です。もちろん、本には複数の著者がいる可能性がありますが、今のところ、多対多の関係の複雑さに立ち入るつもりはありません。

books次のコマンドを使用して、に外部キーを追加します。

ALTER TABLE books ADD COLUMN author_id INT REFERENCES authors(author_id);

次に、いくつかのサンプルデータをテーブルに挿入しましょう。から始めましょうauthors。

INSERT INTO authors (first_name, last_name) 
VALUES (‘Tamsyn’, ‘Muir’), (‘Ann’, ‘Leckie’), (‘Zen’, ‘Cho’);

からすべてを選択しauthorsて、挿入コマンドが機能したことを確認します。

SELECT * FROM authors;

著者テーブルからのすべてのデータのクエリ

次に、いくつかの本のデータをに挿入しbooksます。

INSERT INTO books(title, published_year, author_id) 
VALUES (‘Gideon the Ninth’, 2019, 1), (‘Ancillary Justice’, 2013, 2), (‘Black Water Sister’, 2021, 3);

実行するSELECT * FROM books;と、本のデータが表示されます。

Booksテーブルからのすべてのデータのクエリ

すべてのテーブルを一覧表示する方法

このコマンドを使用し\dtて、データベース内のすべてのテーブルを一覧表示できます。

\dt

あなたがbooks_db見るbooksと。authorsとも表示books_book_id_seqされauthors_author_id_seqます。SERIALこれらは、主キーを生成するために使用したため、テーブルによってIDとして使用される整数のシーケンスを追跡します。

データベース内のすべてのテーブルの一覧表示

テーブルの説明方法

特定のテーブルに関する詳細を表示するには、describetableコマンドを使用できます\d table_name。これにより、列、インデックス、および他のテーブルへの参照が一覧表示されます。

\d table_name

著者テーブルの説明

を使用\dt+ table_nameすると、ストレージや圧縮などの詳細情報が提供されます。

テーブルの名前を変更する方法

テーブルの名前を変更する必要がある場合は、ALTER TABLEコマンドを使用して名前を変更できます。

ALTER TABLE table_name RENAME TO new_table_name;

テーブルを削除する方法

テーブルを削除する場合は、コマンドを使用できますDROP TABLE。

DROP TABLE table_name;

存在しないテーブルを削除しようとすると、エラーが発生します。IF EXISTSステートメントにオプションを含めることで、これを回避できます。このようにして、代わりに通知を受け取ります。

DROP TABLE IF EXISTS table_name;

より長いコマンドとクエリを管理する方法

より長いSQLクエリを作成している場合、コマンドラインはそれを行うための最も人間工学的な方法ではありません。SQLをファイルに書き込んでから、psqlに実行させる方がおそらく良いでしょう。

psqlを使用していて、次のクエリが長くなると思われる場合は、psqlからテキストエディタを開いてそこに書き込むことができます。既存のクエリがある場合、またはサンプルデータをロードするために複数のクエリを実行したい場合は、すでに書き込まれているファイルからコマンドを実行できます。

オプション1-psqlからテキストエディタを開く

コマンドを入力する\eと、psqlはテキストエディタを開きます。エディターを保存して閉じると、psqlは作成したコマンドを実行します。

\e

テキストエディタでコマンドを書く

Windowsでは、psqlのデフォルトのテキストエディタはメモ帳ですが、MacOとLinuxではviです。EDITORコンピューターの環境変数に値を設定することで、これを別のエディターに変更できます。

オプション2–ファイルからコマンドとクエリを実行する

実行するコマンドが特に長い場合や複数のコマンドがある場合は、事前にファイルにSQLを記述し、準備ができたらpsqlにそのファイルを実行させることをお勧めします。

この\iコマンドを使用すると、ファイルを端末に入力したかのように、ファイルから入力を読み取ることができます。

\i path_to_file/file_name.sql

注：このコマンドをWindowsで実行している場合でも、ファイルパスでスラッシュを使用する必要があります。

パスを指定しない場合、psqlはPostgreSQLに接続する前に最後にいたディレクトリでファイルを検索します。

ファイルからのSQLコマンドの実行

クエリの時間を計る方法

クエリにかかる時間を確認したい場合は、クエリの実行タイミングをオンにすることができます。

\timing

これにより、クエリの完了にかかった時間がミリ秒単位で表示されます。

コマンドを再度実行する\timingと、クエリの実行タイミングがオフになります。

クエリ実行タイミングの使用

CSVファイルからデータをインポートする方法

データを含むCSVファイルがあり、これをPostgreSQLデータベースにロードする場合は、コマンドラインからpsqlを使用してこれを行うことができます。

まず、次の構造で呼び出されるCSVファイルを作成しますfilms.csv（Excel、Googleスプレッドシート、Numbers、またはその他のプログラムを使用するかどうかは関係ありません）。

ピクサーフィルムデータを含むスプレッドシート

psqlを開き、films_dbデータベースを作成して接続し、filmsテーブルを作成します。

CREATE DATABASE films_db;

\c films_db

CREATE TABLE films(
	id SERIAL PRIMARY KEY,
	title VARCHAR(100),
	year INT,
	running_time INT
);

次に、コマンドを使用し\copyてCSVファイルをにインポートできますfilms。CSVファイルがコンピューター上にある場所への絶対パスを指定する必要があります。

\copy films(title, year, running_time) FROM 'path_to_file' DELIMITER ‘,’ CSV HEADER;

このDELIMITERオプションは、インポートされるファイルの各行の列を区切る文字をCSV指定し、それがCSVファイルであることをHEADER指定し、ファイルに列の名前を含むヘッダー行が含まれることを指定します。

注：テーブルの列名はのfilms列名と一致する必要はありませんがfilms.csv、同じ順序である必要があります。

SELECT * FROM films;プロセスが成功したかどうかを確認するために使用します。

.csvファイルからのデータのインポート

でデータベースをバックアップする方法pg_dump

データベースをバックアップする必要がある場合pg_dumpは、データベースをSQLスクリプトファイルまたは他の種類のアーカイブファイルに抽出できるユーティリティです。

まず、コマンドライン（psqlではない）で、PostgreSQLbinフォルダーに移動します。

cd "C:\Program Files\PostgreSQL\14\bin"

次に、ユーザー名としてを使用して次のコマンドを実行し、使用postgresするデータベースと出力ファイルを入力します。

pg_dump -U username database_name > path_to_file/filename.sql

ユーザー名にを使用すると、スーパーユーザーのパスワードの入力postgresを求められます。次に、データベースの再作成に必要なSQLコマンドを含むファイルを作成します。postgrespg_dump.sql

データベースを.sqlファイルにバックアップする

出力ファイルのパスを指定しない場合は、pg_dumpPostgreSQLに接続する前に最後にあったディレクトリにファイルを保存します。

Films.sqlバックアップファイルの内容

-vまたは--verboseフラグを渡して、各ステップでpg_dump何が行われているかを確認できます。pg_dump

pg_dumpを冗長モードで実行する

.tarデータベースを（アーカイブ形式）などの他のファイル形式にバックアップすることもできます。

pg_dump -U username -F t database_name > path_to_file/filename.tar

ここで、-Fフラグはpg_dump、出力形式を指定するtことを示し、その.tar形式になることを示します。

データベースを復元する方法

psqlまたはpg_restoreユーティリティを使用して、バックアップファイルからデータベースを復元できます。どちらを選択するかは、データベースの復元元のファイルの種類によって異なります。

1. データベースをなどのプレーンテキスト形式にバックアップした場合は.sql、psqlを使用します。
2. データベースをなどのアーカイブ形式にバックアップした場合は.tar、を使用しますpg_restore。

オプション1-psqlを使用してデータベースを復元する

ファイルからデータベースを復元する.sqlには、コマンドラインで（psqlではなく）を使用しますpsql -U username -d database_name -f filename.sql。

以前に使用したfilms_dbデータベースとfilms.sqlファイルを使用することも、新しいバックアップファイルを作成することもできます。

データを復元するファイル用の空のデータベースを作成します。films.sql復元に使用している場合films_db、最も簡単な方法は、削除films_dbして再作成することです。

DROP DATABASE films_db;

CREATE DATABASE films_db;

別のターミナル（psqlではない）で、次のコマンドを実行しpostgres、ユーザー名、および使用しているデータベースとバックアップファイルの名前を渡します。

psql -U username -d database_name -f path_to_file/filename.sql

フラグはpsql-dに特定のデータベースを指し、-fフラグはpsqlに指定されたファイルから読み取るように指示します。

バックアップファイルのパスを指定しない場合、psqlはPostgreSQLに接続する前に最後にあったディレクトリでファイルを検索します。

スーパーユーザーのパスワードの入力を求めpostgresられ、psqlがデータベースを再作成している間、一連のコマンドがコマンドラインに出力されます。

psqlを使用したデータベースの復元

このコマンドは、復元中に発生したエラーをすべて無視します。エラーが発生した場合にデータベースの復元を停止する場合は、を渡し--set ON_ERROR_STOP=onます。

psql -U username -d database_name --set ON_ERROR_STOP=on -f filename.sql

オプション2–を使用してデータベースを復元するpg_restore

を使用してデータベースを復元するにはpg_restore、を使用しますpg_restore -U username -d database_name path_to_file/filename.tar。

データを復元するファイル用の空のデータベースを作成します。films_dbファイルから復元する場合films.tar、最も簡単な方法は、ファイルを削除films_dbして再作成することです。

DROP DATABASE films_db;

CREATE DATABASE films_db;

コマンドライン（psqlではない）で、次のコマンドを実行しpostgres、ユーザー名、および使用しているデータベースとバックアップファイルの名前を渡します。

pg_restore -U username -d database_name path_to_file/filename.tar

pg_restoreを使用したデータベースの復元

-vまたは--verboseフラグを渡しpg_restoreて、各ステップで何が行われているかを確認することもできます。

詳細モードでのpg_restoreの使用

psqlを終了する方法

psqlを終了し、それを終了する場合は、quitまたはを入力し\qます。

\q

これにより、psqlアプリケーションを使用している場合は閉じ、コマンドラインからpsqlを使用している場合は通常のコマンドプロンプトに戻ります。

ここからどこへ行くか

スキーマ、ロール、テーブルスペースの管理など、psqlで実行できることは他にもたくさんあります。ただし、このガイドは、コマンドラインからPostgreSQLデータベースの管理を開始するのに十分なはずです。

PostgreSQLとリレーショナルデータベースについて引き続き学習する際に、このガイドがお役に立てば幸いです。

このストーリーは、もともとhttps://www.freecodecamp.org/news/manage-postgresql-with-psql/で公開されました

#psql #postgresql 

Administrar Bases De Datos PostgreSQL Desde La Línea De Comandos

Ahora es un buen momento para aprender bases de datos relacionales y SQL. Desde el desarrollo web hasta la ciencia de datos, se utilizan en todas partes.

En la encuesta Stack Overflow 2021 , 4 de las 5 principales tecnologías de bases de datos utilizadas por los desarrolladores profesionales eran sistemas de gestión de bases de datos relacionales.  

PostgreSQL es una excelente opción como primer sistema de administración de bases de datos relacionales para aprender.

1. Es ampliamente utilizado en la industria, incluso en Uber, Netflix, Instagram, Spotify y Twitch .
2. Es de código abierto, por lo que no estará limitado a un proveedor en particular.
3. Tiene más de 25 años y en ese tiempo se ha ganado una reputación de estabilidad y confiabilidad.

Ya sea que esté aprendiendo de la certificación de base de datos relacional freeCodeCamp o esté probando PostgreSQL en su propia computadora, necesita una forma de crear y administrar bases de datos, insertar datos en ellas y consultar datos de ellas.

Si bien existen varias aplicaciones gráficas para interactuar con PostgreSQL, usar psql y la línea de comandos es probablemente la forma más directa de comunicarse con su base de datos.

¿Qué es psql?

psql es una herramienta que le permite interactuar con bases de datos PostgreSQL a través de una interfaz de terminal. Cuando instala PostgreSQL en una máquina, psql se incluye automáticamente.

psql le permite escribir consultas SQL, enviarlas a PostgreSQL y ver los resultados. También le permite usar metacomandos (que comienzan con una barra invertida) para administrar las bases de datos. Incluso puede escribir scripts y automatizar tareas relacionadas con sus bases de datos.

Ahora, ejecutar una base de datos en su computadora local y usar la línea de comando puede parecer intimidante al principio. Estoy aquí para decirles que realmente no es tan malo. Esta guía le enseñará los aspectos básicos de la administración de bases de datos de PostgreSQL desde la línea de comandos, incluido cómo crear, administrar, respaldar y restaurar bases de datos.

Requisito previo: instalar PostgreSQL

Si aún no ha instalado PostgreSQL en su computadora, siga las instrucciones para su sistema operativo en la documentación oficial de PostgreSQL .

Cuando instale PostgreSQL, se le pedirá una contraseña. Guárdelo en un lugar seguro, ya que lo necesitará para conectarse a cualquier base de datos que cree.

Cómo conectarse a una base de datos

Tiene dos opciones cuando usa psql para conectarse a una base de datos: puede conectarse a través de la línea de comando o usando la aplicación psql. Ambos proporcionan prácticamente la misma experiencia.

Opción 1: conectarse a una base de datos con la línea de comando

Abre una terminal. Puede asegurarse de que psql esté instalado escribiendo psql --version. Debería ver psql (PostgreSQL) version_numberdónde version_numberestá la versión de PostgreSQL que está instalada en su máquina. En mi caso, es 14.1.

Comprobando la versión de psql a través de la línea de comando

El patrón para conectarse a una base de datos es:

psql -d database_name -U username

La -dbandera es una alternativa más corta para --dbnamewhile -Ues una alternativa para --username.

Cuando instaló PostgreSQL, se crearon una base de datos y un usuario predeterminados, ambos llamados postgres. Así que ingrese psql -d postgres -U postgrespara conectarse a la postgresbase de datos como postgressuperusuario.

psql -d postgres -U postgres

Se te solicitará una contraseña. Ingrese la contraseña que eligió cuando instaló PostgreSQL en su computadora. El indicador de su terminal cambiará para mostrar que ahora está conectado a la base de postgresdatos.

Conexión a una base de datos desde la línea de comandos con psql

Si desea conectarse directamente a una base de datos como usted mismo (en lugar de como postgressuperusuario), ingrese el nombre de usuario de su sistema como el valor de nombre de usuario.

Opción 2: conectarse a una base de datos con la aplicación psql

Inicie la aplicación psql: se llamará "SQL Shell (psql)". Se le pedirá un servidor, una base de datos, un puerto y un nombre de usuario. Simplemente presione enter para seleccionar los valores predeterminados, que son localhost, postgres, 5432y postgres.

A continuación, se le pedirá la contraseña que eligió cuando instaló PostgreSQL. Una vez que ingrese esto, el indicador de su terminal cambiará para mostrar que está conectado a la base de postgresdatos.

Conexión a una base de datos con la aplicación psql

Nota: si está en Windows, es posible que vea una advertencia como “La página de códigos de la consola (850) difiere de la página de códigos de Windows (1252) Es posible que los caracteres de 8 bits no funcionen correctamente. Consulte la página de referencia de psql 'Notas para usuarios de Windows' para obtener más detalles". No necesita preocuparse por esto en esta etapa. Si desea obtener más información al respecto, consulte la documentación de psql .

Cómo obtener ayuda en psql

Para ver una lista de todos los metacomandos de psql y un breve resumen de lo que hacen, use el \?comando.

\?

comando de ayuda de psql

Si desea ayuda con un comando de PostgreSQL, use \ho \helpy el comando.

\h COMMAND

Esto le dará una descripción del comando, su sintaxis (con partes opcionales entre corchetes) y una URL para la parte relevante de la documentación de PostgreSQL.

psql que describe la instrucción DROP TABLE

Cómo salir de un comando en psql

Si ejecutó un comando que tarda mucho tiempo o imprime demasiada información en la consola, puede salir escribiendo q.

q

Cómo crear una base de datos

Antes de que pueda administrar cualquier base de datos, deberá crear una.

Nota: los comandos SQL deben terminar con un punto y coma, mientras que los metacomandos (que comienzan con una barra invertida) no necesitan hacerlo.

El comando SQL para crear una base de datos es:

CREATE DATABASE database_name;

Para esta guía, vamos a trabajar con datos de libros, así que vamos a crear una base de datos llamada books_db.

CREATE DATABASE books_db;

Cómo enumerar bases de datos

Puede ver una lista de todas las bases de datos disponibles con el comando list.

\l

Listado de todas las bases de datos

Debería ver books_db, así como postgres, template0y template1. (El CREATE DATABASEcomando en realidad funciona copiando la base de datos estándar, llamada template1. Puede leer más sobre esto en la documentación de PostgreSQL ).

El uso \l+mostrará información adicional, como el tamaño de las bases de datos y sus espacios de tabla (la ubicación en el sistema de archivos donde se almacenarán los archivos que representan la base de datos).

\l+

Listado de todas las bases de datos con información adicional

Cómo cambiar bases de datos

Actualmente sigues conectado a la base de datos predeterminada postgres. Para conectarse a una base de datos o cambiar entre bases de datos, use el \ccomando.

\c database_name

Entonces \c books_dblo conectará a la base de books_dbdatos. Tenga en cuenta que el aviso de su terminal cambia para reflejar la base de datos a la que está conectado actualmente.

Cambio de bases de datos

Cómo eliminar una base de datos

Si desea eliminar una base de datos, use el DROP DATABASEcomando.

DROP DATABASE database_name;

Solo podrá eliminar una base de datos si es un superusuario, como postgres, o si es el propietario de la base de datos.

Si intenta eliminar una base de datos que no existe, obtendrá un error. Úselo IF EXISTSpara obtener un aviso en su lugar.

DROP DATABASE IF EXISTS database_name;

Eliminación de una base de datos

No puede eliminar una base de datos que tenga conexiones activas. Entonces, si desea eliminar la base de datos a la que está conectado actualmente, deberá cambiar a otra base de datos.

Cómo crear tablas

Antes de que podamos administrar tablas, debemos crear algunas y completarlas con algunos datos de muestra.

El comando para crear una tabla es:

CREATE TABLE table_name();

Esto creará una tabla vacía. También puede pasar valores de columna entre paréntesis para crear una tabla con columnas. Como mínimo, una tabla básica debe tener una clave principal (un identificador único para diferenciar cada fila) y una columna con algunos datos.

Para nuestro books_db, crearemos una tabla para autores y otra para libros. Para los autores, registraremos su nombre y apellido. Para los libros, registraremos el título y el año en que se publicaron.

Nos aseguraremos de que los autores first_namey last_namelos libros titleno sean nulos, ya que esta es información bastante vital para saber sobre ellos. Para ello incluimos la NOT NULLrestricción.

CREATE TABLE authors(
	author_id SERIAL PRIMARY KEY, 
	first_name VARCHAR(100) NOT NULL, 
	last_name VARCHAR(100) NOT NULL
);

CREATE TABLE books(
	book_id SERIAL PRIMARY KEY, 
	title VARCHAR(100) NOT NULL, 
	published_year INT
);

Verá CREATE TABLEimpreso en la terminal si la tabla se creó correctamente.

Ahora conectemos las dos tablas agregando una clave externa a los libros. Las claves externas son identificadores únicos que hacen referencia a la clave principal de otra tabla. Los libros pueden, por supuesto, tener varios autores, pero no vamos a entrar en las complejidades de las relaciones de muchos a muchos en este momento.

Agregue una clave externa bookscon el siguiente comando:

ALTER TABLE books ADD COLUMN author_id INT REFERENCES authors(author_id);

A continuación, insertemos algunos datos de muestra en las tablas. Empezaremos con authors.

INSERT INTO authors (first_name, last_name) 
VALUES (‘Tamsyn’, ‘Muir’), (‘Ann’, ‘Leckie’), (‘Zen’, ‘Cho’);

Seleccione todo desde authorspara asegurarse de que el comando de inserción funcionó.

SELECT * FROM authors;

Consultar todos los datos de la tabla de autores

A continuación, insertaremos algunos datos de libros en books.

INSERT INTO books(title, published_year, author_id) 
VALUES (‘Gideon the Ninth’, 2019, 1), (‘Ancillary Justice’, 2013, 2), (‘Black Water Sister’, 2021, 3);

Si corres SELECT * FROM books;verás los datos del libro.

Consultar todos los datos de la tabla de libros

Cómo enumerar todas las tablas

Puede usar el \dtcomando para listar todas las tablas en una base de datos.

\dt

Porque books_dbverás booksy authors. También verá books_book_id_seqy authors_author_id_seq. Estos realizan un seguimiento de la secuencia de números enteros utilizados como identificadores por las tablas porque solíamos SERIALgenerar sus claves principales.

Listado de todas las tablas en una base de datos

Cómo describir una tabla

Para ver más información sobre una tabla en particular, puede usar el comando describir tabla: \d table_name. Esto enumerará las columnas, los índices y cualquier referencia a otras tablas.

\d table_name

Describiendo la tabla de autores

El uso \dt+ table_nameproporcionará más información, como sobre el almacenamiento y la compresión.

Cómo cambiar el nombre de una tabla

Si alguna vez necesita cambiar el nombre de una tabla, puede cambiarle el nombre con el ALTER TABLEcomando.

ALTER TABLE table_name RENAME TO new_table_name;

Cómo eliminar una tabla

Si desea eliminar una tabla, puede usar el DROP TABLEcomando.

DROP TABLE table_name;

Si intenta eliminar una tabla que no existe, obtendrá un error. Puede evitar esto al incluir la IF EXISTSopción en la instrucción. De esta manera, recibirá un aviso en su lugar.

DROP TABLE IF EXISTS table_name;

Cómo administrar comandos y consultas más largos

Si está escribiendo consultas SQL más largas, la línea de comandos no es la forma más ergonómica de hacerlo. Probablemente sea mejor escribir su SQL en un archivo y luego hacer que psql lo ejecute.

Si está trabajando con psql y cree que su próxima consulta será larga, puede abrir un editor de texto desde psql y escribirlo allí. Si tiene una consulta existente, o tal vez desee ejecutar varias consultas para cargar datos de muestra, puede ejecutar comandos desde un archivo que ya está escrito.

Opción 1: abre un editor de texto desde psql

Si ingresa el \ecomando, psql abrirá un editor de texto. Cuando guarde y cierre el editor, psql ejecutará el comando que acaba de escribir.

\e

Escribir comandos en un editor de texto

En Windows, el editor de texto predeterminado para psql es Notepad, mientras que en MacOs y Linux es vi. Puede cambiar esto a otro editor configurando el EDITORvalor en las variables de entorno de su computadora.

Opción 2: ejecutar comandos y consultas desde un archivo

Si tiene comandos particularmente largos o múltiples comandos que desea ejecutar, sería mejor escribir el SQL en un archivo con anticipación y hacer que psql ejecute ese archivo una vez que esté listo.

El \icomando le permite leer la entrada de un archivo como si lo hubiera escrito en la terminal.

\i path_to_file/file_name.sql

Nota: si está ejecutando este comando en Windows, aún necesita usar barras diagonales en la ruta del archivo.

Si no especifica una ruta, psql buscará el archivo en el último directorio en el que estuvo antes de conectarse a PostgreSQL.

Ejecutar comandos SQL desde un archivo

Cómo cronometrar consultas

Si desea ver cuánto tardan sus consultas, puede activar el tiempo de ejecución de consultas.

\timing

Esto mostrará en milisegundos el tiempo que tardó en completarse la consulta.

Si \timingvuelve a ejecutar el comando, desactivará el tiempo de ejecución de la consulta.

Uso del tiempo de ejecución de consultas

Cómo importar datos desde un archivo CSV

Si tiene un archivo CSV con datos y desea cargarlo en una base de datos PostgreSQL, puede hacerlo desde la línea de comandos con psql.

Primero, cree un archivo CSV llamado films.csvcon la siguiente estructura (no importa si usa Excel, Google Sheets, Numbers o cualquier otro programa).

Una hoja de cálculo con datos de películas de Pixar

Abra psql y cree una films_dbbase de datos, conéctese a ella y cree una filmstabla.

CREATE DATABASE films_db;

\c films_db

CREATE TABLE films(
	id SERIAL PRIMARY KEY,
	title VARCHAR(100),
	year INT,
	running_time INT
);

Luego puede usar el \copycomando para importar el archivo CSV a films. Debe proporcionar una ruta absoluta a donde se encuentra el archivo CSV en su computadora.

\copy films(title, year, running_time) FROM 'path_to_file' DELIMITER ‘,’ CSV HEADER;

La DELIMITERopción especifica el carácter que separa las columnas en cada fila del archivo que se está importando, CSVespecifica que es un archivo CSV y HEADERespecifica que el archivo contiene una línea de encabezado con los nombres de las columnas.

Nota: No es necesario que los nombres de las columnas de la filmstabla coincidan con los nombres de las columnas, films.csvpero sí deben estar en el mismo orden.

Úselo SELECT * FROM films;para ver si el proceso fue exitoso.

Importación de datos desde un archivo .csv

Cómo hacer una copia de seguridad de una base de datos conpg_dump

Si necesita hacer una copia de seguridad de una base de datos, pg_dumphay una utilidad que le permite extraer una base de datos en un archivo de script SQL u otro tipo de archivo comprimido.

Primero, en la línea de comando (no en psql), navegue a la bincarpeta PostgreSQL.

cd "C:\Program Files\PostgreSQL\14\bin"

Luego ejecute el siguiente comando, usando postgrescomo nombre de usuario y completando la base de datos y el archivo de salida que desea usar.

pg_dump -U username database_name > path_to_file/filename.sql

Úselo postgrespara el nombre de usuario y se le pedirá la postgrescontraseña de superusuario. pg_dumpluego creará un .sqlarchivo que contiene los comandos SQL necesarios para recrear la base de datos.

Copia de seguridad de una base de datos en un archivo .sql

Si no especifica una ruta para el archivo de salida, pg_dumpguardará el archivo en el último directorio en el que estuvo antes de conectarse a PostgreSQL.

Contenido del archivo de copia de seguridad films.sql

Puede pasar la bandera -vo a para ver qué está haciendo en cada paso.--verbosepg_dumppg_dump

Ejecutando pg_dump en modo detallado

También puede hacer una copia de seguridad de una base de datos en otros formatos de archivo, como .tar(un formato de archivo).

pg_dump -U username -F t database_name > path_to_file/filename.tar

Aquí la -Fbandera le dice pg_dumpque va a especificar un formato de salida, mientras tque le dice que va a estar en el .tarformato.

Cómo restaurar una base de datos

Puede restaurar una base de datos desde un archivo de respaldo utilizando psql o la pg_restoreutilidad. El que elija depende del tipo de archivo desde el que está restaurando la base de datos.

1. Si realizó una copia de seguridad de la base de datos en un formato de texto sin formato, como .sql, use psql.
2. Si realizó una copia de seguridad de la base de datos en un formato de archivo, como .tar, use pg_restore.

Opción 1: restaurar una base de datos usando psql

Para restaurar una base de datos desde un .sqlarchivo, en la línea de comando (no en psql), use psql -U username -d database_name -f filename.sql.

Puede utilizar la base de films_dbdatos y films.sqlel archivo que utilizó anteriormente o crear un nuevo archivo de copia de seguridad.

Cree una base de datos vacía para que el archivo restaure los datos. Si está utilizando films.sqlpara restaurar films_db, lo más fácil podría ser eliminarlo films_dby volver a crearlo.

DROP DATABASE films_db;

CREATE DATABASE films_db;

En una terminal separada (no en psql), ejecute el siguiente comando, pasándolo postgrescomo el nombre de usuario y los nombres de la base de datos y el archivo de copia de seguridad que está utilizando.

psql -U username -d database_name -f path_to_file/filename.sql

La -dbandera apunta a psql a una base de datos específica, mientras que la -fbandera le dice a psql que lea desde el archivo especificado.

Si no especifica una ruta para el archivo de respaldo, psql buscará el archivo en el último directorio en el que estuvo antes de conectarse a PostgreSQL.

Se le pedirá la postgrescontraseña de superusuario y luego verá una serie de comandos impresos en la línea de comandos mientras psql recrea la base de datos.

Restaurar una base de datos usando psql

Este comando ignora cualquier error que ocurra durante la restauración. Si desea dejar de restaurar la base de datos si se produce un error, pase --set ON_ERROR_STOP=on.

psql -U username -d database_name --set ON_ERROR_STOP=on -f filename.sql

Opción 2: restaurar una base de datos usandopg_restore

Para restaurar una base de datos usando pg_restore, use pg_restore -U username -d database_name path_to_file/filename.tar.

Cree una base de datos vacía para que el archivo restaure los datos. Si está restaurando films_dbdesde un films.tararchivo, lo más fácil podría ser eliminarlo films_dby volver a crearlo.

DROP DATABASE films_db;

CREATE DATABASE films_db;

En la línea de comando (no en psql), ejecute el siguiente comando, pasándolo postgrescomo nombre de usuario y los nombres de la base de datos y el archivo de copia de seguridad que está utilizando.

pg_restore -U username -d database_name path_to_file/filename.tar

Restaurar una base de datos usando pg_restore

También puede pasar la bandera -vo --verbosepara ver qué pg_restoreestá haciendo en cada paso.

Usando pg_restore en modo detallado

Cómo salir de psql

Si ha terminado con psql y quiere salir de él, ingrese quito \q.

\q

Esto cerrará la aplicación psql si la estaba usando, o lo regresará a su símbolo del sistema habitual si estaba usando psql desde la línea de comandos.

Dónde tomarlo desde aquí

Hay muchas más cosas que puede hacer con psql, como administrar esquemas, roles y espacios de tablas. Pero esta guía debería ser suficiente para comenzar a administrar bases de datos PostgreSQL desde la línea de comandos.

Espero que esta guía le resulte útil a medida que continúa aprendiendo sobre PostgreSQL y las bases de datos relacionales.

Esta historia se publicó originalmente en https://www.freecodecamp.org/news/manage-postgresql-with-psql/

#psql #postgresql 

How to Setting Up PSQL + Typescript + Node in Docker Compose

Setting up psql + typescript + node in docker compose with hot reload.

In this video I am going to show you how to  Setting up psql + typescript + node in docker compose with hot reload.

Code: https://github.com/Chensokheng/rest-api/tree/setup-docker

#psql #typescript #docker #node 

How to Setting Up PSQL + Typescript + Node in Docker Compose

In This Video I Am Going to Show You How To Setting Up Psql + Typescript + Node In Docker Compose with Hot Reload.

Code: https://github.com/Chensokheng/rest-api/tree/setup-docker

#psql #typescript #node #docker 

How To Connect To PostgreSQL Database using Psql

This article covers the most common ways to connect to a PostgreSQL database server via psql command and pgAdmin.

#postgresql #database #psql 

Deploy Node Application and PostgreSql Database to Heroku

Deploying full stack application is awesome thing you can do in full stack development. as deploying on Heroku it looks daunting at first,i put together all steps you need to follow to deploy a working full stack application. It is quick and straight thing to do.

Creating PostgreSql database and setting up express application is out of this blogs scope.

First thing you should do is setting environment variables in .env file , variables which are required for basic application are following.

jwtSecret=lucky11
PG_USER= postgres_user
PG_PASSWORD= password
PG_HOST= localhost
PG_PORT= 5432
PG_DATABASE= database_name
NODE_ENV=development

_Make sure that you __gitignore _the .env file as keeping these environment variables open and accessible is unsafe thing you can do today.

#nodejs #deploy #heroku #psql

Time and Related Data Types in PostgreSQL

We here at Cockroach Labs have been busy getting CockroachDB (CRDB) more in line with PostgreSQL for  v20.1. This allows you to be able to use Cockroach as a drop-in replacement for PostgreSQL (whilst using your favourite existing ORM or driver), but with the powerful, scalable backend we provide.

Time,  Timestamp and  TimestampTZ have been three data types Cockroach has supported, but were not well matched with PostgreSQL functionality. We were also  missing the TimeTZ datatype, as well as precision for  time and  interval types. Even worse, our results did not match PostgreSQL in some cases for the existing data types we do support.

We’ve spent a lot of the  v20.1 release mastering time data types, fixing what was broken through ORM tests and community reports whilst adding new features to bridge that gap. A significant amount of time was spent scratching our heads as we figured out why existing time-related ORM tests were failing against CRDB. We’ve found a lot of devils in the details, with knowledge over these edge cases sparsely located over old SQL mailing lists and StackOverflow.

In this blog post, we’ll explore some of the intricacies of time in PostgreSQL and look at how we reproduce these features using Go and its ecosystem. We’ll share our recommendations for using time along the way.

Are you excited to learn about time in the PostgreSQL world? Join us as a companion in our TARDIS (Time and Related Data Types In SQL…blog post) and hop into an adventure into the world of time. Allons-y!

Session Time Zones

When you open a connection to Postgres/CRDB, you will have a session between you (the client) and the database (the server). The session will contain a time zone, which you can set with the SET TIME ZONE command for both  PostgreSQL and  CRDB.

#postgres #psql

Change a Password for PostgreSQL on Linux via Command Line

PostgreSQL supports many client authentication methods, but in this case we’re only going to concern ourselves with two: password and md5.

Note: The default authentication method for PostgreSQL is ident. If you’d like to change the PostgreSQL authentication method from ident to md5, then visit the linked tutorial!

#technical support #tutorials #authentication #authentication method #centos #database #fedora #md5 #password #postgres #psql #systemctl #systemd #ubuntu