Postgres

First a small comparison with mysql/mariadb commands :

overview table :

mysql	psql
show databases	l
use dbname;	\c dbname;
show tables;	\dt+;
describe tablename;	tablename
select version();	select version();
select now();	select now();
mysqldump	pg_dump

configuration

This is mostly in /var/lib/pgsql/data

pg_hba.conf : PostgreSQL client authentication configuration file

authentication

By default postgres uses IDENT-based authentication and this will never allow you to login via -U (username) and -W (password) options. You need to login as that user and then connect.

su dias psql  

However you can disable it in the pg_hba.conf file by changing the appropriate line for user(s) from 'ident' to 'trust'

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD   
local   all         all                               ident 
host    dias        dias        127.0.0.1/32          trust  

And of course a well-aimed

service postgresql restart  

This method can also be used to give other host access besides localhost :

host    dias        dias        192.168.56.0/24          trust  

And now, if you are on a standard centos 6.6 server the firewall will have the forward rules disabled. You could try to fix that and i will later on (TODO) but for now just disable the firewall since this is an internal test setup. service iptables stop Navicat and other hosts should now be able to reach postgresql.

restore database

In case of a crashes system, you can just run the raw files on the new system.

Important

This was only tested on an exact same version : 9.4 on both systems.

• Install postgres on the new system, and see if it works, you can also query where it keeps the data files:

su - 
su - postgres
psql
show data_directory;
     data_directory
------------------------------
/var/lib/postgresql/9.4/main
(1 row)

If this is a debian machine it probably will be there. My save was from another debian jessie, so the next actions where to copy the old files.

su
/etc/init.d/postgresql stop
/var/lib/postgresql/9.4/
mv main ~/psql_backup
cp -r /media/heok/var/lib/postgresql/9.4/main/ .
chown -R postgres.postgres main
/etc/init.d/postgresql start

Check again and you will find the tables if all went well.

su - postgres
psql
l
c stock
dt
     List of relations
 Schema |   Name   | Type  | Owner 
--------+----------+-------+-------
public | daily    | table | stock
public | intraday | table | stock
public | ticker   | table | stock
(3 rows)

find files

For instance the log and config file, they are indeed in ridiculous places. To find the config file will help since that will hint where the log file is as well. Login to psql as manager and run :

show config_file
config_file               
-----------------------------------------
/etc/postgresql/11/main/postgresql.conf
(1 row)

Open it and look for the log file :

log_statement = 'all'
log_directory = 'pg_log'

Of course it does not say where this pg_log directory is under, so :

sudo updatedb
locate pg_log
/var/lib/postgresql/11/main/pg_log

An equally ridiculous location, but at least we have a log

tail -f /var/lib/postgresql/11/main/pg_log

create as user

For test purposes in the backend i needed a database to be instantiated from scratch with stable id's to be able to test it with json queries.

This database has to created from scratch each time to get room for the correct ID's and most creation actions require admin privileges. The best (easiest) way to do it is to create a test user like this :

sudo su 
su - postgres
createuser --interactive --pwprompt

Now fill in the name and password and be sure to answer yes to :

shall the new role be a superuser?

Now you still cannot log in because the database does not exist, and it will not work from within an sql script:

drop database kloptest; # fails !!
create database kloptest; # fails !!

But you can drop and create these as use kloptest outside psql with the commands:

dropdb -U kloptest kloptest
createdb -U kloptest kloptest

Now the easiest way to reinstall a complete test environment is to generate an .sql file with the pg_dump command. First fill the normal database as you please and then dump it with this command :

pg_dump -c -U klopt klopt > testdb.sql

Note that nowhere in this .sql file is the database name mentioned, and you can just re-import this as new in kloptest :

psql -U kloptest < testdb.sql 
psql -U kloptest < testdb.sql 
psql -U kloptest < testdb.sql 

I repeated it on purpose so you can see there are no errors about existing tables.

Important

If you redo this with a larger test database your tests will probably work because it starts numbering id's from 1 again. But if you are going to use names probably first find out how to seed 'faker' to get the same data as well.

troubleshooting

too many connections

The main problem here surfaced with a crash with this message :

Error

FATAL: remaining connection slots are reserved for non-replication superuser connections

It seems this is a matter of to many open connections. First let's see what up while still in gdb.

You can view all open connections with this query in psql :

select *
from pg_stat_activity
where datname = 'klopt';

Or a number :

select count(*) from pg_stat_activity where datname = 'klopt';

This print 97 suggesting that the limit might be 100 ?! That is indeed what seems to be the limit. However if you do all database the count is 102

select count(*) from pg_stat_activity;
count 
-------
102
(1 row)

So it might be per database because otherwise why the extra 2 ?!

If we look further in the records, they almost all seem to be COMMIT's :

37406 | klopt   | 24363 |    37405 | klopt    |                  | 127.0.0.1   |                 |       41088 | 2019-12-03 17:06:31.686818+01 |                               | 2019-12-03 17:06:31.693572+01 | 2019-12-03 17:06:31.693725+01 | Client          | ClientRead | idle   |             |              | COMMIT                          | client backend
37406 | klopt   | 24478 |    37405 | klopt    |                  | 127.0.0.1   |                 |       41404 | 2019-12-03 17:07:04.480472+01 |                               | 2019-12-03 17:07:04.493644+01 | 2019-12-03 17:07:04.4937+01   | Client          | ClientRead | idle   |             |              | COMMIT                          | client backend
37406 | klopt   | 24477 |    37405 | klopt    |                  | 127.0.0.1   |                 |       41398 | 2019-12-03 17:07:04.346908+01 |                               | 2019-12-03 17:07:04.354322+01 | 2019-12-03 17:07:04.354353+01 | Client          | ClientRead | idle   |             |              | COMMIT                          | client backend

If we release the debug session, the count drops to 1 probably the psql session itself. So if we restart and try some actions from the website we indeed that every action rises the count with 1 or more connections.

If we look at the commands for filling orders and vehicles (happens upon each refresh) we see in orders_from_db_list() :

Database db(constants::conninfo);

...

return tbl;

So the database simply goes out of scope, probably not the culprit? A test with printing Open and Close in the constructor/destructor also suggests that. It prints :

Init ... 
GET : /ws/rest/orders
Opening db
GET : /ws/rest/trips
Opening db
Closeing db !?!
GET : /ws/rest/vehicles
Opening db

So trips and vehicles are prime suspects, and indeed in trips and vehicles there were still lines like this :

Database *db = new Database(constants::conninfo);
// change to :
Database db(constants::conninfo);

So these ALL have to be altered !!. In any file search for new and delete, remove them and recompile and you get a tour along all errors. This change will change the behavior of F5, which will now at least for refresh keep a count of 1 open connection. This now considered fixed.!! (monitor)

show database table counts

For the actual count :

SELECT schemaname,relname,n_live_tup
FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;

But who is going to remember that ?!

Here is what you do if you only want to see which tables are empty:

\dt+;

It gives the next table, all tables with 8192 or less bytes are empty !! I do not know what the 8192 bytes contains, the 8192 bytes are probably used for keys, because simple tables with only INT fields are 0 bytes. Mostly you can expect a table with any rows in it to be 16KB or more !

Schema |   Name    | Type  | Owner |    Size    | Description 
-------+-----------+-------+-------+------------+-------------
public | locations | table | klopt | 8192 bytes | 
public | orders    | table | klopt | 8192 bytes | 
public | products  | table | klopt | 8192 bytes | 
public | trips     | table | klopt | 8192 bytes | 
public | trucks    | table | klopt | 16KB       | 
public | vehicles  | table | klopt | 8192 bytes | 
8 rows)

select fail

Mostly because the tables do not belong to the user. Check with :

\dt
     List of relations
 Schema |   Name    | Type  | Owner 
--------+-----------+-------+-------
 public | locations | table | postgres
 public | orders    | table | postgres
 public | products  | table | postgres
 public | stages    | table | postgres
 public | trips     | table | klopt
 public | vehicles  | table | klopt
(6 rows)

Just as an example, tables trips and vehicles would be 'select' able by klopt, the other would give an error.

ALTER TABLE locations OWNER TO klopt;
ALTER TABLE orders OWNER TO klopt;
.. etc

If you want to see the privileges :

\du

     Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
bag       |                                                            | {}
klopt     |                                                            | {}
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

As you see you don't need explicit privileges as long as you are owner !

ERROR: permission denied for database klopt

This message seems to say that your password was wrong but it is actually fairly clear.

Important

It is NOT the connection that fails but an operation !!

I encountered this when connecting to postgresql through libpqxx :

To debug this, try to connect by hand and also show the log messages :

psql -U klopt # if this connects, your password is checked and ok !
# now try to run the query with tghe log running :
sudo tail -f /var/log/postgresql/postgresql-11-main.log
# result was :
2020-11-09 14:32:59.513 CET [8673] klopt@klopt ERROR:  permission denied for database klopt
2020-11-09 14:32:59.513 CET [8673] klopt@klopt STATEMENT:  CREATE SCHEMA IF NOT EXISTS "public"

So more lines than the program reports. And indeed if you run this query inside psql it will say the same.

The solution in this case was :

GRANT ALL PRIVILEGES ON DATABASE klopt TO klopt;
GRANT ALL PRIVILEGES ON all TABLES IN SCHEMA public TO klopt;

schemas with @ at symbol

We use schema inside the postgres database for planner to separate users. However the unique handle we use for that is the email address. And postgres will complain if you just say :

select * from kees@klopt.org.vehicles;
ERROR:  syntax error at or near "@"
LINE 1: select * from test@klopt.org.vehicles;

Also this will fail :

select * from "kees@klopt.org.vehicles";
ERROR:  relation "test@klopt.org.vehicles" does not exist
LINE 1: select * from "test@klopt.org.vehicles";

The CLI of postgres will give you a hint if you use the TAB :

select * from "<TAB>
# expands to : 
"kees@klopt.org".   "stats@klopt.org".  "test@klopt.org".
# and the next line expands to : 
select * from "test<TAB>
select * from "test@klopt.org".
# just fill in the table after the dot:
select * from "test@klopt.org".vehicles;

This will work.

Important

With weird characters, put the schema name in "quotes" and not the table.

psql empty id's

This bugged me hard because it looks like there are lines here without an id but in fact this is psql's way of displaying multiline data !!

id | title | primary_author 
----+-------+----------------
1   | kees  | 1             +
    |       | 2 en ook 3    +
    |       |               +
    |       | 

This line was created with the following command :

update books set primary_author='1
2 en ook 3

' where id=1;

The +'s mean continued on the next line, and it is indeed 1 database row.