notes for my pg-scripts repro developement and tests

notes for my pg-scripts repro developement and tests

--https://stackoverflow.com/questions/36959/how-do-you-use-script-variables-in-psql
psql -f pg-scripts/loadtest/create_bench1_user_and_db.sql -v password=xxx


vi ~/.pgpass
localhost:5432:*:bench1:xxx

pgbench --help
pgbench -i -s 15 -h localhost -p 5432 -U bench1  -d bench
[localhost:postgres:~] # cat ~/.pgpass



-- The synyax fo psql -v variables with strings is fiddly
-- https://stackoverflow.com/questions/7389416/postgresql-how-to-pass-parameters-from-command-line
[localhost:postgres:~] # psql -v newpassword="'qwe123'"
psql (8.4.20, server 9.3.5)
WARNING: psql version 8.4, server version 9.3.
         Some psql features might not work.
Type "help" for help.

(postgres@[local]:5432) 12:55:26 [postgres]
# select :newpassword;
 ?column?
----------
 qwe123
(1 row)


Issue#001
-- 'Peer authentication failed for user' errors
[postgres01:postgres:~/pg-scripts] # vi ~/.pgpass
[postgres01:postgres:~/pg-scripts] # psql -U bench1
psql: FATAL:  Peer authentication failed for user "bench1"
Solution/workaround
-- https://stackoverflow.com/questions/18664074/getting-error-peer-authentication-failed-for-user-postgres-when-trying-to-ge 
-- for TD database should be something like
[postgres01:postgres:~] # tail -50 /var/lib/pgsql/9.4/data/pg_hba.conf | grep md5
# METHOD can be "trust", "reject", "md5", "password", "gss", "sspi",
# "password" sends passwords in clear text; "md5" is preferred since
local   all         all                               md5
host    all     all     127.0.0.1/32                    md5
host    all     all             ::1/128                 md5
-- NB - unusally(?) my pg_ctl wasn't in the postgres user path - so gave full path : /usr/pgsql-9.4/bin/pg_ctl restart



[postgres01:postgres:~] # ssh-keygen
...
Your public key has been saved in /var/lib/pgsql/.ssh/id_rsa.pub.
...
[postgres01:postgres:~] # cat /var/lib/pgsql/.ssh/id_rsa.pub
ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEAtY1a...




bench1-> \copy (SELECT * FROM pgbench_branches) to 'pgbench_branches.csv' with csv
COPY 3
bench1-> \copy (SELECT * FROM pgbench_accounts) to 'pgbench_accounts.csv' with csv
COPY 300000
bench1-> \d pgbench_accounts
   Table "public.pgbench_accounts"
  Column  |     Type      | Modifiers
----------+---------------+-----------
 aid      | integer       | not null
 bid      | integer       |
 abalance | integer       |
 filler   | character(84) |
Indexes:
    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)

bench1-> \d pgbench_branches
   Table "public.pgbench_branches"
  Column  |     Type      | Modifiers
----------+---------------+-----------
 bid      | integer       | not null
 bbalance | integer       |
 filler   | character(88) |
Indexes:
    "pgbench_branches_pkey" PRIMARY KEY, btree (bid)


using scott demo Oracle schema

conn / as sysdba
create tablespace SCOTT_DATA datafile '/home/oracle/dbf/ORACLE/scott_data.dbf' size 25M autoextend on maxsize 1000M;
create user scott identified by *** default tablespace SCOTT_DATA;
grant connect,resource to scott;
ALTER USER scott quota unlimited on scott_data;

conn scott/***
create table pgbench_branches (bid number, bbalance number, filler character(88), CONSTRAINT pgbench_branches_pk PRIMARY KEY (bid));
create table pgbench_accounts (aid number, bid number, bbalance number, filler character(88), CONSTRAINT pgbench_accounts_pk PRIMARY KEY (aid));

was getting:
Record 1: Rejected - Error on table PGBENCH_BRANCHES.
ORA-01950: no privileges on tablespace 'SCOTT_DATA'
fixed via: 
ALTER USER scott quota unlimited on scott_data;
https://stackoverflow.com/questions/21671008/ora-01950-no-privileges-on-tablespace-users


$ sqlldr scott/*** load_branches.ctl

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Sep 25 14:34:04 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 3

Table PGBENCH_BRANCHES:
  3 Rows successfully loaded.

Check the log file:
  load_branches.log
for more information about the load.
$ cat load_branches.ctl
load data
infile pgbench_branches.csv
into table pgbench_branches
fields terminated by ','
(bid, bbalance)


 $ sqlldr scott/*** load_accounts.ctl

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Sep 25 14:37:21 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 64
Commit point reached - logical record count 128
Commit point reached - logical record count 192
...
Commit point reached - logical record count 299828
Commit point reached - logical record count 299892
Commit point reached - logical record count 299956
Commit point reached - logical record count 300000

Table PGBENCH_ACCOUNTS:
  300000 Rows successfully loaded.

Check the log file:
  load_accounts.log
for more information about the load.

$ cat load_accounts.ctl
load data
infile pgbench_accounts.csv
into table pgbench_accounts
fields terminated by ','
(aid, bid, bbalance)


SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

@?/rdbms/admin/utlxplan.sql
@?/sqlplus/admin/plustrce.sql
GRANT plustrace TO scott;
https://www.morganslibrary.org/reference/autotrace.html


         


git master vs develop
 261  git clone git@github.com:dgapitts/pg-scripts.git
  262  cd pg-scripts
  263  ls ../pg-scripts_bak/loadtest/
  264  mmkdir create_bench1_user_and_db.sql
  265  mkdir loadtest
  266  cp ../pg-scripts_bak/loadtest/create_bench1_user_and_db.sql loadtest
  267  git status
  268  git add loadtest
  269  git status
  270  git add .
  271  git commit -m "initial postgres db and user setup - called bench1 as many of the tests will be pgbench related"
  272  git status
  273  git log
  274  git push
  275  git status
  276  git checkout -b develop
  277  git push
  278  git status
  279  vi README.md
  280  git status
  281  git add README.md
  282  git status
  283  git commit -m "minor push for develop branch config"
  284  git config --local user.name "Dave Pitts"
  285  git config --global user.name "Dave Pitts"
  286  git config --global user.email "dgapitts@gmail.com"
  287  git status
  288  git push
  289  git branch
  290  git remote -v
  291  git push
  292  git push origin develop
  293  git checkout master
  294  git merge develop
  295  git push
  296  h40
[postgres01:postgres:~/pg-scripts] # git checkout develop
Switched to branch 'develop'