How to perform pgbench initialization into a new database using a nondefault user

For the database pgbenchdb creation refer to https://oracledbaplanner.blogspot.com/2023/03/how-to-create-postgresql-database-from.html

In this blog we will initialize the tables used for stress testing using pgbench utility

Command: pgbench -U pgbenchusr -i -s 50 pgbenchdb

pgbench - binary that comes by default with postgresqlserver,lib,PGDG install (not even contrib is needed).
-U is user to connect to for loading the data
-i is to perform initialization step
-s is the scale factor
pgbenchdb is the db where we will be loading the data

Actual command output:

-bash-4.2$ pgbench -U pgbenchusr -i -s 50 pgbenchdb dropping old tables... NOTICE:  table "pgbench_accounts" does not exist, skipping NOTICE:  table "pgbench_branches" does not exist, skipping NOTICE:  table "pgbench_history" does not exist, skipping NOTICE:  table "pgbench_tellers" does not exist, skipping creating tables... generating data (client-side)... 5000000 of 5000000 tuples (100%) done (elapsed 22.13 s, remaining 0.00 s) vacuuming... creating primary keys... done in 33.64 s (drop tables 0.00 s, create tables 0.02 s, client-side generate 22.22 s, vacuum 2.76 s, primary keys 8.65 s). -bash-4.2$

Verify the data load:

postgres=# \l+                                                                      List of databases    Name    |   Owner    | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |      Description -----------+------------+----------+-------------+-------------+-----------------------+---------+------------+----------- ---------------------------------  pgbenchdb | pgbenchusr | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 756 MB  | pg_default |  postgres  | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8769 kB | pg_default | default ad ministrative connection database  template0 | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8617 kB | pg_default | unmodifiab le empty database            |            |          |             |             | postgres=CTc/postgres |         |            |  template1 | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8617 kB | pg_default | default te mplate for new databases            |            |          |             |             | postgres=CTc/postgres |         |            | (4 rows)   postgres=#   postgres=# \c pgbenchdb You are now connected to database "pgbenchdb" as user "postgres". pgbenchdb=# \dt+                                           List of relations  Schema |       Name       | Type  |   Owner    | Persistence | Access method |  Size   | Description --------+------------------+-------+------------+-------------+---------------+---------+-------------  public | pgbench_accounts | table | pgbenchusr | permanent   | heap          | 641 MB  |  public | pgbench_branches | table | pgbenchusr | permanent   | heap          | 40 kB   |  public | pgbench_history  | table | pgbenchusr | permanent   | heap          | 0 bytes |  public | pgbench_tellers  | table | pgbenchusr | permanent   | heap          | 56 kB   | (4 rows)   pgbenchdb=# select relname,relpages,reltuples from pg_class where relname like 'pgbench%' and relkind='r';      relname      | relpages | reltuples ------------------+----------+-----------  pgbench_accounts |    81968 |     5e+06  pgbench_branches |        1 |        50  pgbench_history  |        0 |         0  pgbench_tellers  |        3 |       500 (4 rows)   pgbenchdb=#

Verify mountpoint utilization:

-bash-4.2$ df -h /pgdata

Filesystem                          Size  Used Avail Use% Mounted on

/dev/mapper/appdata--vg-pgdata--lv   10G   84M   10G   1% /pgdata

-bash-4.2$

 

After load:

-bash-4.2$ df -h /pgdata

Filesystem                          Size  Used Avail Use% Mounted on

/dev/mapper/appdata--vg-pgdata--lv   10G  1.3G  8.8G  13% /pgdata

-bash-4.2$

Thank you!