postgresql自动化脚本

Question

请帮助我在Ubuntu上安装postgresql之后，如何运行postgresql的自动化脚本？在使用之前，我需要自动化DB准备(创建表、插入数据、更改权限)。

我需要用用户当前的权限来完成它。例如，我有唯一的用户admin_ubuntu。他完全有权经营牛皮泥。所有脚本都编写好了，但是我如何运行脚本呢？通常，我需要编辑信任(我相信，/etc/postgresql/9.1/main/pg_hba.conf)。但我不想这么做。

所以，我需要的只是运行sql，这将产生很多事情。我怎么做才能运行它呢？问题是，当操作系统是完美的(空的)时，我需要做很多事情来允许运行sql。

它将在每次Ubuntu安装时制作。

Answer 1

您应该能够运行shell供应-下面是您可以做的事情的示例：

# creating user
sudo -u postgres psql -c "CREATE USER admin WITH PASSWORD 'password';"
# creating new db if needed .. might need 2 (dev/test)
createdb -U vagrant mydevdb

# if you have more complex things you'll need to put that in a create_db.sql file and run the script as
sudo -u postgres psql < create_db.sql

create_db.sql文件可以包含任何CREATE TABLE语句。

Answer 2

在这里共享一些脚本，展示如何创建数据库、角色、架构和表。这并不意味着被视为最佳实践或可接受的生产，但希望它将有助于开始使用PostgreSQL。任何被_ (例如，_variable_ )包围的代码片段，都表示应该相应替换的字符串。标准的shell命令以$开头。

PostgreSQL安装

我的环境是一个Ubuntu服务器/容器，其中安装和启动了PostgreSQL：

$ apt install postgresql-12
$ pg_ctlcluster 12 main start

这会自动添加一个postgres Linux用户，该用户在PostgreSQL中具有superuser权限，允许使用以下方法测试安装：

$ sudo su postgres
$ psql

这应该会导致类似于postgres=#的提示符。如果是这样的话，您应该能够按照下面的步骤使用默认的/etc/postgresql/12/main/pg_hba.conf设置。

数据库和角色创建

将其保存到setup.sql文件后，可以使用$ sudo -u postgres psql < setup.sql运行

CREATE ROLE testadmin WITH LOGIN CREATEDB PASSWORD 'secret5';
CREATE DATABASE testdb OWNER testadmin;

CREATE ROLE _current-linux-user_ WITH LOGIN CREATEDB INHERIT;

GRANT pg_read_server_files TO testadmin;
GRANT pg_read_server_files TO _current-linux-user_;
GRANT testadmin to _current-linux-user_;

在玩游戏时，从零开始运行$ sudo -u postgres psql < teardown.sql是很有用的

DROP DATABASE testdb;
DROP ROLE testadmin;
DROP ROLE _current-linux-user_;

从CSV文件加载数据库

我们创建一个与当前用户具有相同登录名的角色是有原因的。它允许我们通过简单的$ psql testdb连接到数据库，它显示了一个类似于testdb=>的提示符。

首先，我们需要CSV文件来填充testdb数据库，我使用下面两个文件作为示例食品数据库。小心不要在文件末尾留下空行，否则会有一个ERROR: missing data for column。

categories.csv

Category ID,Category Name
1,Fruit
2,Nut
3,Vegetable
4,Grain
5,Fungus
6,Alga
7,Seed

items.csv

Food Name,Category,Nutrition
Peach,1,"Vitamin A, C, Potassium, Magnesium, Iron"
Brazil nut,7,"Iron, Calcium, Protein"
Broccoli,3,"Vitamin C, Magnesium"
Bean,4,"Magnesium, Iron, Calcium, Protein"
Mushroom,5,"Iron, Magnesium, Sodium, Protein"

现在我们可以运行$ psql testdb < schema.sql了

CREATE SCHEMA food
        CREATE TABLE food.categories (category_id integer PRIMARY KEY, category text)
        CREATE TABLE food.items (id serial, name text, category_id integer REFERENCES food.categories (category_id), nutrition text);

/* Load data from CSV files into tables */
COPY food.categories(category_id, category)
    FROM '/path/to/categories.csv' WITH (FORMAT csv, HEADER ON);
COPY food.items(name, category_id, nutrition)
    FROM '/path/to/items.csv' WITH (FORMAT csv, HEADER ON);

/* Test */
SELECT * FROM food.categories;
SELECT * FROM food.items;
SELECT name,category FROM food.items INNER JOIN food.categories
        ON food.items.category_id = food.categories.category_id;

这将产生以下结果：

CREATE SCHEMA
COPY 7
COPY 5
 category_id | category
-------------+-----------
           1 | Fruit
           2 | Nut
           3 | Vegetable
           4 | Grain
           5 | Fungus
           6 | Alga
           7 | Seed
(7 rows)

 id |    name    | category_id |                nutrition
----+------------+-------------+------------------------------------------
  1 | Peach      |           1 | Vitamin A, C, Potassium, Magnesium, Iron
  2 | Brazil nut |           7 | Iron, Calcium, Protein
  3 | Broccoli   |           3 | Vitamin C, Magnesium
  4 | Bean       |           4 | Magnesium, Iron, Calcium, Protein
  5 | Mushroom   |           5 | Iron, Magnesium, Sodium, Protein
(5 rows)

    name    | category
------------+-----------
 Peach      | Fruit
 Brazil nut | Seed
 Broccoli   | Vegetable
 Bean       | Grain
 Mushroom   | Fungus
(5 rows)

指定CSV格式允许我们将默认分隔符( , )括在数据列中的引号中。默认的加载是text，这可能导致ERROR: extra data after last expected column。此错误的另一个来源是忘记包含COPY命令中的所有字段，例如nutrition。

使用新用户登录

那么我们刚刚创建的testadmin用户呢？我们可以使用密码连接到数据库，如下所示：

$ psql -U testadmin -d testdb -h localhost
Password for user testadmin:
psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

testdb=>

如果您忘记使用-h localhost，您可能会得到一个psql: error: FATAL: Peer authentication failed for user "testadmin"。

上面的命令在优秀文献上有官方网站。