When you use: sudo -u postgres psql my_db you run psql as the (Linux) user postgres, therefor the .psql_history file is in the home directory of the postgres user (e.g. /home/postgres/.psql_history), not in the home directory of the user you originally logged in with. In some installations, the location of the postgres user’s home directory … Read more
According to Postgresql 9.6 Release Notes the idle_in_transaction_session_timeout parameter was introduced in version 9.6. E.2.3.1.10. Server Configuration Allow sessions to be terminated automatically if they are in idle-in-transaction state for too long (Vik Fearing) This behavior is controlled by the new configuration parameter idle_in_transaction_session_timeout. It can be useful to prevent forgotten transactions from holding locks … Read more
The default behavior of the import is that it replaces all roles it does not know with the role you are doing the import with. So depending on what you need the database for, you might just be fine with importing it and with ignoring the error messages. Quoting from http://www.postgresql.org/docs/9.2/static/backup-dump.html#BACKUP-DUMP-RESTORE Before restoring an SQL … Read more
table permissions: select * from information_schema.role_table_grants where grantee=”YOUR_USER” ; ownership: select * from pg_tables where tableowner=”YOUR_USER” ; schema permissions: select r.usename as grantor, e.usename as grantee, nspname, privilege_type, is_grantable from pg_namespace join lateral ( SELECT * from aclexplode(nspacl) as x ) a on true join pg_user e on a.grantee = e.usesysid join pg_user r on … Read more
According to PostgreSQL documentation, there are both the inline and the block style comments. The inline style: SELECT 23 AS test — this is just a test The block style: /* The following is a very * non-trivial SQL code */ SELECT 42 AS result
Psql uses a system viewer to show its output in the console. In bash it likely uses less for the scrollable/page-able features it provides. To use a different viewer or use different settings, you just need to set the PAGER environment variable. Running psql to use less with the -S or –chop-long-lines option seemed to … Read more
export PATH=/usr/pgsql-9.2/bin:$PATH The program executable psql is in the directory /usr/pgsql-9.2/bin, and that directory is not included in the path by default, so we have to tell our shell (terminal) program where to find psql. When most packages are installed, they are added to an existing path, such as /usr/local/bin, but not this program. So … Read more
use PERFORM statement – http://www.postgresql.org/docs/current/static/plpgsql-statements.html Sometimes it is useful to evaluate an expression or SELECT query but discard the result, for example when calling a function that has side-effects but no useful result value. To do this in PL/pgSQL, use the PERFORM statement so it’s just DO $$ BEGIN PERFORM my_function(); END $$;
to execute commands against a running container use docker exec. to copy a file (ex: dump.sql) into a container, use docker cp So your approach might look something like this: docker cp ./dump.sql pg_test:/docker-entrypoint-initdb.d/dump.sql docker exec -u postgres pg_test psql postgres postgres -f docker-entrypoint-initdb.d/dump.sql here it is in generic form: docker cp ./localfile.sql containername:/container/path/file.sql docker … Read more
Postgres psql needs escaping for capital letters. Eonil=# \d+ “TestTable1” So this works well. Eonil=# \d+ “TestTable1” Table “public.TestTable1” Column | Type | Modifiers | Storage | Description ——–+——————+———–+———-+————- ID | bigint | not null | plain | name | text | | extended | price | double precision | | plain | Indexes: “TestTable1_pkey” … Read more