Not an Ideal Configuration
PostgreSQL was created such that the permissions internal to the database are intended to mirror the system directly. You can get around that and it won't be too painful, but you're supposed to run psql as user1 and user2 per your example.
Now what you're asking to do is an especially bad idea. postgres isn't a regular user, it's a ...
Create unix user accounts for each person, and set user-name maps in pg_ident so that everyone connects as postgres.
or alternatively create individual database users for each account and give them superuser privs. Optionally "set session authenticaton postgres" can be set as a startup ...
pg_total_relation_size() sums up everything that belongs to a table, including bloat.
But \dt in psql does not list all tables of a database, it only includes schemas listed in the current search_path. Check with:
System tables in pg_catalog are always excluded. And there may be additional schemas. To see all tables in the database:
Your insert syntax is incorrect, it should look like insert into <table> select <something> from <table> -- not like insert into <table> (something, something, (select .... And don't sprinkle parentheses at random -- they are meaningful.
INSERT INTO samples
SELECT nextval('my_sequence'), 'newsample', locations
FROM samples WHERE ...
\ds without argument doesn't show sequences that are not accessible through the current search_path, whereas information_schema.sequences ignores the search path.
On the other hand, information_schema.sequences won't show sequences that the current user cannot actually use, whereas \ds won't filter those out. According to the documentation:
Only those ...