psql -h localhost -d mydatabase -U myuser -p [port]
進入 postgres
sudo -u postgres psql $ psql -h localhost -d neurv_v1test -U postgres
修改密碼
# Replace xxxxxxx with your own password
ALTER USER postgres WITH ENCRYPTED PASSWORD 'xxxxxxx';
進入不同的資料庫
psql DBNAME USERNAME
show all databases
\l
show all tables
方法一
\dt
方法二
SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';
建使用者
CREATE USER manuel WITH PASSWORD 'jw8s0F4' CREATEDB;
CREATE ROLE myuser LOGIN PASSWORD 'mypass';
建資料庫並授權 CREATE DATABASE mydatabase WITH OWNER = myuser; ALTER DATABASE name OWNER TO new_owner;
匯入資料庫 $ psql -h localhost -d neurv_v1test -U postgres < neurv_2019-05-07_110633.dmp psql -p [port] -h [domain] -d [database] -U [username] < [fileName] psql PS: -h 一定要加
匯出資料庫 pg_dump neurv_v1test -s > pgdump_backup.sql pg_dump neurv_v1test > pgdump_backup.dmp pg_dump -h domain_name -U user_name -d database_name --column-inserts --data-only --table=table_name > xxxxxx.sql
免在輸入一次密碼
https://gist.github.com/vielhuber/96eefdb3aff327bdf8230d753aaee1e1
# linux
PGPASSWORD="password" pg_dump --no-owner -h host -p port -U username database > file.sql
# windows
PGPASSWORD=password&& pg_dump --no-owner -h host -p port -U username database > file.sql
# alternative
pg_dump --no-owner --dbname=postgresql://username:password@host:port/database > file.sql
# restore
psql --set ON_ERROR_STOP=on -U postgres database < file.sql
# backup exluding table
pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --exclude-table=foo database > tmp.sql
# backup including table
pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username --table=foo database > tmp.sql
# backup 並壓縮
pg_dump -F c -f backup.tar.gz -U
https://qastack.cn/server/440923/pg-dump-backup-compression
# backup and restore
PGPASSWORD=password && pg_dump --no-owner -h 127.0.0.1 -p 5432 -U username database > tmp.sql
psql -U postgres -d database -c "drop schema public cascade; create schema public;"
psql --set ON_ERROR_STOP=on -U postgres -d database -1 -f tmp.sql
rm tmp.sql
http://www.londatiga.net/it/database-it/how-to-use-postgresql-pgdump-in-crontab-without-password/
For example, the pg_dump command in following cron job will not work:
5 0 * * * pg_dump -U username -Fc dbname > ~/backup/database/mydb_backup.gz
Fortunately, Postgresql has a Password File ‘.pgpass‘ feature that we can use to overcome this problem. Create a ~/.pgpass file in user’s home directory with the following format:
hostname:port:database:username:password
Ex: localhost:5432:mydatabase:lorenz:lorensxyz
Then don’t forget to change its’ permission to 0600 (chmod 0600 ~/.pgpass). pg_dump will use the password from .pgpass file and the cronjob will work successfully.