Warning: is_dir(): open_basedir restriction in effect. File(/tmp) is not within the allowed path(s): (/data/web/virtuals/98151/virtual) in /data/web/virtuals/98151/virtual/www/subdom/postgresql/wp-content/plugins/wp-simple-firewall/src/lib/vendor/fernleafsystems/wordpress-services/src/Core/Fs.php on line 465

Warning: Cannot modify header information - headers already sent by (output started at /data/web/virtuals/98151/virtual/www/subdom/postgresql/wp-content/plugins/wp-simple-firewall/src/lib/vendor/fernleafsystems/wordpress-services/src/Core/Fs.php:465) in /data/web/virtuals/98151/virtual/www/subdom/postgresql/wp-content/plugins/disable-xml-rpc-pingback/disable-xml-rpc-pingback.php on line 51
Cheet sheets Archives - PostgreSQL Candies

Dump and restore postgresql users (roles)

dump on source database: pg_dumpall -r > sourceroles.backup restore on target database: if postgresql is freshly installed you will have to switch to postgres user: sudo su postgres restore under postgres user: psql < sourceroles.backup...

psql quick help

All command can be followed by “mask” to show only some objects. tablespaces: list all tablespaces with sizes: \db+ ┬ádatabases: connect to some database: \c dbname list all: \l+ schemas: \dn+ users, roles: \dg+ extensions: list installed extensions: \dx...

Useful regular expressions

column contains some upper case letters: column ~ E'[[:upper:]]' column contains some lower case letters: column ~...