将Postgres表从DNA Center导出到CSV

您所在的位置：网站首页 › grepgz文件 › 将Postgres表从DNA Center导出到CSV

将Postgres表从DNA Center导出到CSV

#将Postgres表从DNA Center导出到CSV| 来源: 网络整理| 查看: 265

简介

本文档介绍如何将Postgres数据库表从Cisco DNA Center导出到CSV以排除问题并创建备份。

要求

本文档中的信息基于以下软件和硬件版本：

Cisco DNA Center软件版本2.3.4.0

注：对于2.3.X.X及更高版本，您需要位于受限制外壳内

登录到Postgres数据库第 1 步

建议您在主Postgres实例(postgres-0)上。

magctl service exec -D postgres-0 -m maglev-registry.maglev-system.svc.cluster.local:5000/postgres 'su postgres -c "repmgr cluster show"'

示例：

$ magctl service exec -D postgres-0 -m maglev-registry.maglev-system.svc.cluster.local:5000/postgres 'su postgres -c "repmgr cluster show"' INFO: connecting to database Role | Name | Upstream | Connection String ----------+----------------------------------------------|----------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------ * master | postgres-0.postgres.fusion.svc.cluster.local | | user=replicauser password=j6puHsKb host=postgres-0.postgres.fusion.svc.cluster.local dbname=replica_db port=5433 connect_timeout=10 standby | postgres-2.postgres.fusion.svc.cluster.local | postgres-0.postgres.fusion.svc.cluster.local | user=replicauser password=j6puHsKb host=postgres-2.postgres.fusion.svc.cluster.local dbname=replica_db port=5433 connect_timeout=10 standby | postgres-1.postgres.fusion.svc.cluster.local | postgres-0.postgres.fusion.svc.cluster.local | user=replicauser password=j6puHsKb host=postgres-1.postgres.fusion.svc.cluster.local dbname=replica_db port=5433 connect_timeout=10

注意：您可以从任何节点进行选择查询，但只能在主节点上进行写入。

步骤 2

输入Postgres数据库。

docker exec -it $(docker ps | awk '/postgres_postgres-[0-2]+_fusion/ {print $1}') psql -U apic_em_user -d campus -h localhost

示例：

maglev@maglev-master-10-88-244-151:~$ docker exec -it $(docker ps | awk '/postgres_postgres-[0-2]+_fusion/ {print $1}') psql -U apic_em_user -d campus -h localhost psql (11.5 (Debian 11.5-3.pgdg90+1)) Type "help" for help. campus=# 备份PostgreSQL表(.sql)

建议在进行任何修改（UPDATE、DELETE或INSERT操作）之前备份这些表，以便在出现故障时恢复这些表。

备份特定PostgresSQL表

运行下一个命令将特定Postgres表备份到SQL文件中：

注意：在受限制的shell内（而不是Postgres数据库中）运行命令

read -p "What table would you like to backup? echo $'\n> '" dbtable; docker exec -it $(docker ps | awk '/postgres_postgres-[0-2]+_fusion/ {print $1}') pg_dump -U apic_em_user -t $dbtable campus > /data/tmp/$dbtable.sql && echo "Table backup has been created at /data/tmp/$dbtable.sql"

示例：

maglev@maglev-master-10-88-244-151:~$ read -p "What table would you like to backup? echo $'\n> '" dbtable; docker exec -it $(docker ps | awk '/postgres_postgres-[0-2]+_fusion/ {print $1}') pg_dump -U apic_em_user -t $dbtable campus > /data/tmp/$dbtable.sql && echo "Table backup has been created at /data/tmp/$dbtable.sql" What table would you like to backup? echo $'\n> 'apwrlsscnfgrtnhswrlssgrpng Table backup has been created at /data/tmp/apwrlsscnfgrtnhswrlssgrpng.sql maglev@maglev-master-10-88-244-151:~$ 备份多个表

运行下一个命令将多个Postgres表备份到SQL文件中：

dbtables=( ); for dbtable in ${dbtables[@]}; do docker exec -it $(docker ps | awk '/postgres_postgres-[0-2]+_fusion/ {print $1}') pg_dump -U apic_em_user -t $dbtable campus > /data/tmp/$dbtable.sql && echo "Table backup has been created at /data/tmp/$dbtable.sql"; done

示例：

maglev@maglev-master-10-88-244-151:~$ dbtables=("wirelessgrouping" "apwrlsscnfgrtnhswrlssgrpng" "wrlssgrpng_apmacaddrlist" "wrlssgrpng_lclvlnidlst"); maglev@maglev-master-10-88-244-151:~$ for dbtable in ${dbtables[@]}; do docker exec -it $(docker ps | awk '/postgres_postgres-[0-2]+_fusion/ {print $1}') pg_dump -U apic_em_user -t $dbtable campus > /data/tmp/$dbtable.sql && echo "Table backup has been created at /data/tmp/$dbtable.sql"; done Table backup has been created at /data/tmp/wirelessgrouping.sql Table backup has been created at /data/tmp/apwrlsscnfgrtnhswrlssgrpng.sql Table backup has been created at /data/tmp/wrlssgrpng_apmacaddrlist.sql Table backup has been created at /data/tmp/wrlssgrpng_lclvlnidlst.sql maglev@maglev-master-10-88-244-151:~$ 备份PostgreSQL数据库第 1 步

连接到Postgres数据库容器：

警告：备份的内容可能很大，这取决于环境存储能否快速使用。请谨慎继续。

docker exec -it $(docker ps | awk '/postgres_postgres-[0-2]+_fusion/ {print $1}')

示例：

maglev@maglev-master-10-88-244-151:~$ docker exec -it $(docker ps | awk '/postgres_postgres-[0-2]+_fusion/ {print $1}') bash root@postgres-0:/# 步骤 2

连接到容器后，运行下一命令：

pg_dump -U apic_em_user -h localhost campus -v | gzip > campus.sql.gz

示例：

root@postgres-0:/# pg_dump -U apic_em_user -h localhost campus -v | gzip > campus.sql.gz ... pg_dump: dumping contents of table "public.rdynmcsrvc6_ports" pg_dump: processing data for table "public.reachabilitysession" pg_dump: dumping contents of table "public.reachabilitysession" pg_dump: processing data for table "public.reachableap" pg_dump: dumping contents of table "public.reachableap" pg_dump: processing data for table "public.reapaclconfig" pg_dump: dumping contents of table "public.reapaclconfig" pg_dump: processing data for table "public.reapaclip ... root@postgres-0:/# ls -lha | grep campus -rw-r--r-- 1 root root 705M Mar 28 21:21 campus.sql.gz

注：请注意，可能需要一些时间

复制到逗号分隔值(CSV)文件

要运行下一条命令，请注意有关如何登录Postgres数据库的说明，因为命令是在数据库中运行的。

将表复制到CSV文件

运行下一个命令将表复制到CSV文件。

COPY ' CSV HEADER;

示例：

campus=# COPY wrlssgrpng_apmacaddrlist TO '/tmp/wrlssgrpng_apmacaddrlist.csv' CSV HEADER; COPY 11 campus=#

提示：要验证文件是否成功创建，您需要连接到Postgres容器并转到所选路径。

将查询结果复制到CSV文件 COPY ( ) TO ' ' WITH DELIMITER ',' CSV HEADER;

示例：

campus=# COPY (select * from wrlssgrpng_apmacaddrlist where instanceid = 549654106) TO '/tmp/wrlssgrpng_apmacaddrlist.csv' WITH DELIMITER ',' CSV HEADER; COPY 9 campus=# 将文件从Postgres容器复制到Cisco DNA Center主文件系统第 1 步

确定Postgres容器ID:

docker ps | grep postgres_postgres

示例：

maglev@maglev-master-10-88-244-151:~$ docker ps | grep postgres_postgres 8b9a3e5823c0 7e7f65972748 "/usr/bin/dumb-init …" 2 weeks ago Up 2 weeks k8s_postgres_postgres-0_fusion_f1b6c9aa-c076-4b77-af2a-ac977d4d57ff_1 步骤 2

运行命令以从容器复制到主文件系统。

docker cp :

示例：

maglev@maglev-master-10-88-244-151:~$ docker cp 8b9a3e5823c0:/tmp/wrlssgrpng_apmacaddrlist.csv wrlssgrpng_apmacaddrlist.csv maglev@maglev-master-10-88-244-151:~$ ls -lha | grep csv -rw-r--r-- 1 maglev maglev 277 Mar 28 23:19 wrlssgrpng_apmacaddrlist.csv TO '

【本文地址】

将Postgres表从DNA Center导出到CSV

将Postgres表从DNA Center导出到CSV

今日新闻

推荐新闻