postgres : pg_dump and restore

test_utf8=# \l+
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8     | en_US.UTF-8     |        |           |                       | 7686 kB | pg_default | default
administrative connection database
 sales     | salesapp | UTF8     | libc            | en_US.UTF-8     | en_US.UTF-8     |        |           | =Tc/salesapp         +| 7782 kB | salesspace |
           |          |          |                 |                 |                 |        |           | salesapp=CTc/salesapp |         |            |
 template0 | postgres | UTF8     | libc            | en_US.UTF-8     | en_US.UTF-8     |        |           | =c/postgres          +| 7529 kB | pg_default | unmodifi
able empty database
           |          |          |                 |                 |                 |        |           | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | libc            | en_US.UTF-8     | en_US.UTF-8     |        |           | =c/postgres          +| 7734 kB | pg_default | default
template for new databases
           |          |          |                 |                 |                 |        |           | postgres=CTc/postgres |         |            |
 test      | postgres | LATIN9   | libc            | sv_SE.iso885915 | sv_SE.iso885915 |        |           |                       | 7886 kB | pg_default |
 test_utf8 | postgres | UTF8     | libc            | sv_SE.UTF-8     | sv_SE.UTF-8     |        |           |                       | 8134 kB | pg_default |






[postgres@localhost ~]$ pg_dump test_utf8 | gzip > test_utf8.zip
Password:

[postgres@localhost ~]$ pwd
/var/lib/pgsql


-rw-r--r--. 1 postgres postgres 65268 Feb 25 09:37 test_utf8.zip



[postgres@localhost ~]$ ls -ltr
total 64
drwx------. 4 postgres postgres    51 Feb 20 18:42 17
drwxr-xr-x. 3 root     root        24 Feb 23 08:10 tablespaces
-rw-r--r--. 1 postgres postgres 65268 Feb 25 09:37 test_utf8.zip
[postgres@localhost ~]$


test_utf8=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# drop database test_utf8;
DROP DATABASE
postgres=#



postgres-# \l+
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8     | en_US.UTF-8     |        |           |                       | 7686 kB | pg_default | default
administrative connection database
 sales     | salesapp | UTF8     | libc            | en_US.UTF-8     | en_US.UTF-8     |        |           | =Tc/salesapp         +| 7782 kB | salesspace |
           |          |          |                 |                 |                 |        |           | salesapp=CTc/salesapp |         |            |
 template0 | postgres | UTF8     | libc            | en_US.UTF-8     | en_US.UTF-8     |        |           | =c/postgres          +| 7529 kB | pg_default | unmodifi
able empty database
           |          |          |                 |                 |                 |        |           | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | libc            | en_US.UTF-8     | en_US.UTF-8     |        |           | =c/postgres          +| 7734 kB | pg_default | default
template for new databases
           |          |          |                 |                 |                 |        |           | postgres=CTc/postgres |         |            |
 test      | postgres | LATIN9   | libc            | sv_SE.iso885915 | sv_SE.iso885915 |        |           |                       | 7886 kB | pg_default |





postgres-# CREATE DATABASE test_utf8
postgres-#   WITH ENCODING 'UTF8'
postgres-#        LC_COLLATE='sv_SE.UTF-8'
postgres-#        LC_CTYPE='sv_SE.UTF-8'
postgres-#        TEMPLATE=template0;
ERROR:  syntax error at or near "gunzip"
LINE 1: gunzip -c /var/lib/pgsql/test_utf8.zip | test_utf8
        ^
postgres=# CREATE DATABASE test_utf8
postgres-#   WITH ENCODING 'UTF8'
postgres-#        LC_COLLATE='sv_SE.UTF-8'
postgres-#        LC_CTYPE='sv_SE.UTF-8'
postgres-#        TEMPLATE=template0;
CREATE DATABASE
postgres=# GRANT ALL PRIVILEGES ON DATABASE test_utf8 TO vihaan;
GRANT
postgres=# \l+
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8     | en_US.UTF-8     |        |           |                       | 7686 kB | pg_default | default
administrative connection database
 sales     | salesapp | UTF8     | libc            | en_US.UTF-8     | en_US.UTF-8     |        |           | =Tc/salesapp         +| 7782 kB | salesspace |
           |          |          |                 |                 |                 |        |           | salesapp=CTc/salesapp |         |            |
 template0 | postgres | UTF8     | libc            | en_US.UTF-8     | en_US.UTF-8     |        |           | =c/postgres          +| 7529 kB | pg_default | unmodifi
able empty database
           |          |          |                 |                 |                 |        |           | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | libc            | en_US.UTF-8     | en_US.UTF-8     |        |           | =c/postgres          +| 7734 kB | pg_default | default
template for new databases
           |          |          |                 |                 |                 |        |           | postgres=CTc/postgres |         |            |
 test      | postgres | LATIN9   | libc            | sv_SE.iso885915 | sv_SE.iso885915 |        |           |                       | 7886 kB | pg_default |
 test_utf8 | postgres | UTF8     | libc            | sv_SE.UTF-8     | sv_SE.UTF-8     |        |           | =Tc/postgres         +| 8198 kB | pg_default |
           |          |          |                 |                 |                 |        |           | postgres=CTc/postgres+|         |            |
           |          |          |                 |                 |                 |        |           | vihaan=CTc/postgres   |         |            |





bash ---

[postgres@localhost ~]$ pwd
/var/lib/pgsql
[postgres@localhost ~]$ gunzip -c /var/lib/pgsql/test_utf8.zip |psql test_utf8
Password for user postgres:
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "test_utf8" does not exist
[postgres@localhost ~]$ gunzip -c /var/lib/pgsql/test_utf8.zip | psql -U postgres -d test_utf8
Password for user postgres:
SET
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 1309
ALTER TABLE
GRANT



postgres=# \l+
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8     | en_US.UTF-8     |        |           |                       | 7686 kB | pg_default | default
administrative connection database
 sales     | salesapp | UTF8     | libc            | en_US.UTF-8     | en_US.UTF-8     |        |           | =Tc/salesapp         +| 7782 kB | salesspace |
           |          |          |                 |                 |                 |        |           | salesapp=CTc/salesapp |         |            |
 template0 | postgres | UTF8     | libc            | en_US.UTF-8     | en_US.UTF-8     |        |           | =c/postgres          +| 7529 kB | pg_default | unmodifi
able empty database
           |          |          |                 |                 |                 |        |           | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | libc            | en_US.UTF-8     | en_US.UTF-8     |        |           | =c/postgres          +| 7734 kB | pg_default | default
template for new databases
           |          |          |                 |                 |                 |        |           | postgres=CTc/postgres |         |            |
 test      | postgres | LATIN9   | libc            | sv_SE.iso885915 | sv_SE.iso885915 |        |           |                       | 7886 kB | pg_default |
 test_utf8 | postgres | UTF8     | libc            | sv_SE.UTF-8     | sv_SE.UTF-8     |        |           | =Tc/postgres         +| 8198 kB | pg_default |
           |          |          |                 |                 |                 |        |           | postgres=CTc/postgres+|         |            |
           |          |          |                 |                 |                 |        |           | vihaan=CTc/postgres   |         |            |

postgres=#