从PostgreSQL数据库中提取数据而不使用pg_dump

问题描述：

有一个PostgreSQL数据库，我只有有限的访问权限（例如，我不能使用pg_dump）。我试图通过从数据库导出某些表创建本地“镜像”。我没有从psql中将表转换为SQL所需的权限。现在，我只是有一个Python脚本，通过我的table_names迭代，选择所有字段，然后它们导出为CSV：从PostgreSQL数据库中提取数据而不使用pg_dump

for table_name, file_name in zip(table_names, file_names): 
    cmd = """echo "\\\copy (select * from %s)" to stdout WITH CSV HEADER | psql -d remote_db | gzip > ./%s/%s.gz"""%(table_name,dir_name,file_name) 
    os.system(cmd)

我想如果可能的话不使用CSV，因为我失去了字段类型和编码可能会搞砸。首先最好的方法可能是使用\ copy为表获取表的生成SQL代码。接下来最好的将是XML，理想情况下用某种方法保留字段类型。如果这不起作用，我认为最后的选择可能是两个查询---一个是获取字段数据类型，另一个是获取实际数据。

任何想法或建议将不胜感激 - 谢谢！

你如何连接到数据库？ ODBC，JDBC（Jython），Pygres，psycopg？ – 2010-05-05 05:59:27

你不能使用pg_dump的情况如何？你认为你必须拥有什么样的权限才能使用pg_dump？ – 2010-05-05 09:03:40

答

它困扰我一点关于“我没有权限所需的只是从psql中转储为SQL表。pg_dump独立运行，外部psql（都是客户端），如果您有权连接到数据库并选择一个表格，我猜你也可以使用pg_dump -t <table>转储它。我错过了什么吗？

答

你可以使用这些查询（得到通过使用“PSQL --echo隐藏”和“\ d”），以获得基本的元数据：

-- GET OID 
SET oid FROM pg_class WHERE relname = <YOUR_TABLE_NAME> 

-- GET METADATA 
SELECT a.attname, 
    pg_catalog.format_type(a.atttypid, a.atttypmod), 
    (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) 
    FROM pg_catalog.pg_attrdef d 
    WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), 
    a.attnotnull, a.attnum 
FROM pg_catalog.pg_attribute a 
WHERE a.attrelid = <YOUR_TABLES_OID_FROM_PG_CLASS> AND a.attnum > 0 AND NOT a.attisdropped 
ORDER BY a.attnum;

这给了你的名字，数据类型，默认，行中的空标志和字段顺序。要获得实际的数据，您最好的选择仍然是CSV - 内置COPY表格使用CSV HEADER进行标准输出非常强大。但是如果您担心编码问题，请确保在转储CSV数据之前获取server_encoding和client_encoding的值。结合上述查询的元数据应该提供足够的信息来正确解释CSV转储。

答

如果使用psycopg2，则可以使用cursor.description来检查列名称，并使用提取的数据类型将其转换为所需的字符串（如数据）为可接受的格式。

此代码创建INSERT语句，你不仅可以使用在PostgreSQL，也可以与其他数据库（那么你很可能将不得不改变日期格式）：

cursor.execute("SELECT * FROM %s" % (table_name)) 
column_names = [] 
columns_descr = cursor.description 
for c in columns_descr: 
    column_names.append(c[0]) 
insert_prefix = 'insert into %s (%s) values ' % (table_name, ', '.join(column_names)) 
rows = cursor.fetchall() 
for row in rows: 
    row_data = [] 
    for rd in row: 
     if rd is None: 
      row_data.append('NULL') 
     elif isinstance(rd, datetime.datetime): 
      row_data.append("'%s'" % (rd.strftime('%Y-%m-%d %H:%M:%S'))) 
     else: 
      row_data.append(repr(rd)) 
    print('%s (%s);' % (insert_prefix, ', '.join(row_data)))

在psycopg2有用于COPY甚至支持。看看：关于COPY的相关方法their docs

如果你更喜欢使用元数据，那么你可以使用我的配方：Dump PostgreSQL db schema to text。它基于Extracting META information from PostgreSQL由洛伦佐艾伯顿

从PostgreSQL数据库中提取数据而不使用pg_dump

相关推荐