Mari bermain PostgreSQL¶

SELECT
    datid,
    datname,
    usesysid,
    usename,
    application_name,
    client_addr,
    state,
    query
FROM pg_stat_activity;

SELECT * FROM pg_stat_activity;

-- rename
ALTER DATABASE "old-db-name" RENAME TO "new-db-name";
-- owner
ALTER DATABASE "db-name" OWNER TO db-user-owner;

-- set role
ALTER USER db-user-name WITH CREATEDB;
-- rename role
ALTER USER old_user_name RENAME TO new_user_name;

-- set role
ALTER ROLE db-role-name CREATEROLE CREATEDB SUPERUSER;
-- rename role
ALTER USER old_user_name RENAME TO new_user_name;

#
DBUSER=''
DBNAME=''
DBBKPNAME=''
BKPFORMAT=''
# 
pg_dump -U $DBUSER -W -F t $DBNAME > /var/lib/postgres/data/$DBBACKUPNAME.$FORMAT
# -U postgres:  specifies the user to connect to PostgreSQL database server. We used postgres in this example.

# -W:  forces pg_dump to prompt for the password before connecting to the PostgreSQL database server. After you hit enter, pg_dump will prompt for the password of postgres user.

# -F : specifies the output file format that can be one of the following:

# c: custom-format archive file format
# d: directory-format archive
# t:tar
# p: plain text SQL script file)

#
su - postgres-user -s /bin/bash
#
createdb new-db-to-restore -T template1 -O new-db-owner
pg_restore -v -d new-db-to-restore /path/to/dump/file.dump
#
pg_restore /tmp/201801130000.wow.dump \
    -d new-db-to-restore \
    -O db-user-owner \
    -j 2 \
    -h db-host-ip \
    -U db-user-name
    -W \
#
pg_restore /tmp/201801130000.wow.dump \
    -d new-db-to-restore \
    -O db-user-owner \
    -j 2
#
pg_restore /tmp/201801130000.wow.dump \
    -C -d postgres \
    -O db-user-owner \
    -j 2
#
createdb -T template1 new-db-name
pg_restore -C -d newly-created-db /path/db-name.dump
#
pg pg_restore -l db-name.dump > db-name-dump.list

#
psql -h db-host-ip -U postgres-user -d postgres -W
#
psql -U {user-name} -d {desintation_db} -f {dumpfilename.sql}
# 
sed 's/OWNER TO olduser/OWNER TO newuser/g' <  /path/to/dump.sql | psql dbname

CREATE DATABASE db-name WITH TEMPLATE template1;

--
GRANT ALL PRIVILEGES ON db-name TO db-user-name;
-- grant just for read only
GRANT CONNECT ON DATABASE dbName TO userName;
GRANT USAGE ON SCHEMA public TO userName;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO userName;

--
CREATE USER "db_user" WITH NOCREATEROLE NOSUPERUSER CREATEDB;

--
DROP USER "dbUser";