Maintenance

  • Home
  • Log in
  • « A bit of a gotcha with git externals in svn
  • PostgreSQL: handy SQL »

PostgreSQL XML dump

Posted by gregster on 31 Jan 2020 in Announcements

PostgreSQL (psql) has several xml output methods.

For example, to dump a table to an XML file:

# /usr/pgsql-9.6/bin/psql -v ON_ERROR_STOP=1 -d name_of_db
name_of_db=# COPY (SELECT table_to_xml('name_of_schema.name_of_table', true, false, '')) to '/path/to/backup.xml'

There are two problems with using XML outputs:
1) The XML is pretty basic
2) Because of the way PostgreSQL works the whole transaction is done at once so memory allocation on large tables will kill the process (or the server) and you end up with messages like this:

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

The advice from sysadmin is don't use XML output for big things.

This entry was posted by Greg and filed under Announcements.

Maintenance

This blog is the location for all work involving software and hardware maintenance, updates, installs, etc., both routine and urgent.
  • Home
  • Recently
  • Archives
  • Categories

Search

Categories

  • All
  • Announcements
  • Hit by a bus
  • Labs
    • Activity log
    • Documentation
  • Notes
  • R & D
    • Activity log
    • Documentation
  • Servers
    • Activity log
    • Documentation
  • Tasks

All blogs

  • Academic
  • AdaptiveDB
  • Admin
  • Announcements
  • CanMys
  • Cascade
  • CGWP
  • ColDesp
  • Depts
  • DVPP
  • Endings
  • HCMC Blogs
  • Landscapes
  • LEMDO
  • Linguistics
  • Maint
  • LondonMap
  • Mariage
  • MoM
  • Moses
  • Pro-D
  • Projects
  • ScanCan
  • HumsSites
  • Wendat

This collection ©2022 by admin • Help • Free blog engine