连接用ssh连接到远程postgresql数据库

pg.md

配置和连接

pg_hba.conf

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

映射远程端口到本地

ssh -L 63333:localhost:5432 root@pg.mofe.me -f -N

连接到远程服务器

psql -h loc -p 63333 -U pg

tips

重启postgresql

/etc/init.d/postgresql restart

修改用户密码

\password

拷贝excel导出的csv文件到服务器

COPY customer FROM '/home/pg/customer2.csv' CSV HEADER ;

从服务器拷贝到本地

\copy (select * from customers where bought_date >'2015-3-1' and bought_date < '2015-07-01') to '~/1.csv' WITH (FORMAT CSV);

修改sequence的开始值

ALTER SEQUENCE serial RESTART WITH 105;