Thread: problem with backup and restore (probaly stupit newb thing)
Can anyone help me out on this (I will need to backup and restore the data base, but am not savy on the proper save and restore syntax). I used the default ones in PGadmin as detailed below and it blew up on pg_restore: restoring data for table "tblaction" pg_restore: ERROR: invalid byte sequence for encoding "UNICODE": 0xe9 CONTEXT: COPY tblaction, line 1799, column value: "Chargé" pg_restore: [archiver (db)] error returned by PQendcopy pg_restore: *** aborted because of error Backup command= D:\Program Files\pgAdmin III\pg_dump.exe -i -h 192.168.123.112 -p 5432 -U postgres -F c -b -v -f "D:\backups\postgrescompressed.backup" wazagua Restorecommand= D:\Program Files\pgAdmin III\pg_restore.exe -i -h 192.168.123.112 -p 5432 -U postgres -d waztest -v "D:\backups\postgrescompressed.backup" Using ServerVersion: 07.03.0200 PostgreSQL 7.4.6 on i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2 Resulting error text = D:\Program Files\pgAdmin III\pg_restore.exe -i -h 192.168.123.112 -p 5432 -U postgres -d waztest -v "D:\backups\postgrescompressed.backup" pg_restore: connecting to database for restore pg_restore: creating DUMP TIMESTAMP DUMP TIMESTAMP pg_restore: creating SCHEMA public pg_restore: creating COMMENT SCHEMA public pg_restore: creating FUNCTION plpgsql_call_handler() pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 9; 1255 16207920 FUNCTION plpgsql_call_handler() postgres pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near "OWNER" at character 46 Command was: ALTER FUNCTION public.plpgsql_call_handler() OWNER TO postgres; pg_restore: creating PROCEDURAL LANGUAGE plpgsql pg_restore: creating FUNCTION inserttabledata(character varying, character varying) pg_restore: [archiver (db)] Error from TOC entry 10; 1255 16227934 FUNCTION inserttabledata(character varying, character varying) postgres pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near "$" at character 94 Command was: CREATE FUNCTION inserttabledata(character varying, character varying) RETURNS boolean AS $_$ declare @clinum ALIAS FOR... pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near "OWNER" at character 77 Command was: ALTER FUNCTION public.inserttabledata(character varying, character varying) OWNER TO postgres; pg_restore: creating TABLE tblcase pg_restore: [archiver] Error from TOC entry 1672; 1259 12375856 TABLE tblcase postgres pg_restore: [archiver] could not set default_tablespace to "": ERROR: unrecognized configuration parameter "default_tablespace" pg_restore: [archiver] could not set default_with_oids: ERROR: unrecognized configuration parameter "default_with_oids" pg_restore: creating TABLE tblexportmarkedrecords pg_restore: creating VIEW case_marked_exp pg_restore: creating TABLE tbllocation pg_restore: creating VIEW casenumber_wrong_loc pg_restore: creating TABLE tblcdacases pg_restore: creating TABLE tblcompany pg_restore: creating TABLE tbllastexportedcaseid pg_restore: creating VIEW casesforcda pg_restore: creating TABLE tblassociate pg_restore: creating VIEW csi_associates pg_restore: creating VIEW dupe_cli_case_incda pg_restore: creating VIEW dupecasenumbers pg_restore: creating VIEW duped_assoc pg_restore: creating TABLE tblaction pg_restore: creating TABLE tblcasesource pg_restore: creating TABLE tblcasetype pg_restore: creating TABLE tblcustomer pg_restore: creating TABLE tbldistrict pg_restore: creating TABLE tbldivision pg_restore: creating TABLE tblethnicity pg_restore: creating TABLE tblidentificationtype pg_restore: creating TABLE tbljobtitle pg_restore: creating TABLE tblmerchandise pg_restore: creating TABLE tblmethod pg_restore: creating TABLE tblmilitarybranch pg_restore: creating TABLE tblmilitarystatus pg_restore: creating TABLE tbloffensetype pg_restore: creating TABLE tblotherperson pg_restore: creating TABLE tblpatrontype pg_restore: creating TABLE tblregion pg_restore: creating TABLE tblsex pg_restore: creating TABLE tblstaffexec pg_restore: creating TABLE tblstatus pg_restore: creating TABLE tblsubjecttype pg_restore: creating TABLE tbluser pg_restore: creating TABLE tblworktype pg_restore: creating VIEW viwcaseexport pg_restore: creating VIEW nrma_view pg_restore: WARNING: column "affiliatecode" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore: WARNING: column "affilateincidentnumber" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore: WARNING: column "middlename" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore: WARNING: column "address" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore: WARNING: column "city" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore: WARNING: column "state" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore: WARNING: column "zip" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore: WARNING: column "race" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore: WARNING: column "eyecolor" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore: WARNING: column "haircolor" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore: WARNING: column "height" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore: WARNING: column "weight" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore: WARNING: column "gender" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore: WARNING: column "prosecuted" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore: WARNING: column "signedstatement" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore: creating TABLE tblproblemcodes pg_restore: creating TABLE tbltrans pg_restore: creating VIEW problemcodes_originalid pg_restore: creating TABLE tbl_i2an_default_values pg_restore: creating TABLE tblalarmcall pg_restore: creating TABLE tblalarmsystem pg_restore: creating TABLE tblallfields pg_restore: creating TABLE tblanswer_defaults pg_restore: creating TABLE tblanswers pg_restore: creating TABLE tblassociatethirdpartyaccounts pg_restore: creating TABLE tblaudit pg_restore: creating TABLE tblauditassignments pg_restore: creating TABLE tblauditstatus pg_restore: creating TABLE tblaudittypes pg_restore: creating TABLE tblaward pg_restore: creating TABLE tblawardtype pg_restore: creating TABLE tblbadpasswords pg_restore: creating TABLE tblboolean pg_restore: creating TABLE tblcamera pg_restore: creating TABLE tblcaselptools pg_restore: creating TABLE tblcasepayments pg_restore: creating TABLE tblcaserestnotes pg_restore: creating TABLE tblcaseresttrackpaycheck pg_restore: creating TABLE tblcaseresttrackpaymentcycle pg_restore: creating TABLE tblcaseresttrackpaymentstatus pg_restore: creating TABLE tblcaseresttrackpaymenttype pg_restore: creating TABLE tblcaseresttracktype pg_restore: creating TABLE tblcdacasestemp pg_restore: creating TABLE tblcdacasetransfered pg_restore: creating TABLE tblcdaclient pg_restore: creating TABLE tblcdaorglevel pg_restore: creating TABLE tblcdapayment pg_restore: creating TABLE tblcdapaymenttemp pg_restore: creating TABLE tblcdastore pg_restore: creating TABLE tblcdasyscode pg_restore: creating TABLE tblclientdatacapture pg_restore: creating TABLE tblcountry pg_restore: creating TABLE tblcreditcardtype pg_restore: creating TABLE tblcube pg_restore: creating TABLE tblcubeline pg_restore: creating TABLE tblcubesave pg_restore: creating TABLE tblcubesaveline pg_restore: creating TABLE tblcustinc pg_restore: creating TABLE tblcustincattitude pg_restore: creating TABLE tblcustinccustomer pg_restore: creating TABLE tblcustincinjuredpersons pg_restore: creating TABLE tblcustincinvolvedvehicle pg_restore: creating TABLE tblcustinclighting pg_restore: creating TABLE tblcustincnotes pg_restore: creating TABLE tblcustincproduct pg_restore: creating TABLE tblcustincproperty pg_restore: creating TABLE tblcustincshoes pg_restore: creating TABLE tblcustincvehicleloss pg_restore: creating TABLE tblcustincwitnesslog pg_restore: creating TABLE tbldamagelevel pg_restore: creating TABLE tbldatadictionaryheader pg_restore: creating TABLE tbldatadictionarylinepresentation pg_restore: creating TABLE tbldepartment pg_restore: creating TABLE tbldiscountcode pg_restore: creating TABLE tbldisposition pg_restore: creating TABLE tbldotcarrier pg_restore: creating TABLE tbldotdistribution pg_restore: creating TABLE tbldotsealincident pg_restore: creating TABLE tbldottrucksealincidenttype pg_restore: creating TABLE tbldriverboolean pg_restore: creating TABLE tbldriverfrequency pg_restore: creating TABLE tbldriverinformation pg_restore: creating TABLE tbldriverlicclass pg_restore: creating TABLE tbldrivernotes pg_restore: creating TABLE tbldriverpurpose pg_restore: creating TABLE tbldropdowntables pg_restore: creating TABLE tbldtsfields pg_restore: creating TABLE tblempinc pg_restore: creating TABLE tblempincabsence pg_restore: creating TABLE tblempincbodypart pg_restore: creating TABLE tblempinccause pg_restore: creating TABLE tblempincequipment pg_restore: creating TABLE tblempincfirstaider pg_restore: creating TABLE tblempincfirstaidetype pg_restore: creating TABLE tblempincillness pg_restore: creating TABLE tblempincinjury pg_restore: creating TABLE tblempincinsurancereportstatus pg_restore: creating TABLE tblempincinuredpersons pg_restore: creating TABLE tblempincinvolvedvehicle pg_restore: creating TABLE tblempincio pg_restore: creating TABLE tblempincnotes pg_restore: creating TABLE tblempincppe pg_restore: creating TABLE tblempincproduct pg_restore: creating TABLE tblempincproperty pg_restore: creating TABLE tblempincpropertydamage pg_restore: creating TABLE tblempincpropertystolen pg_restore: creating TABLE tblempincsa pg_restore: creating TABLE tblempincspecificarea pg_restore: creating TABLE tblempincsq pg_restore: creating TABLE tblempincstatus pg_restore: creating TABLE tblempincsummaryprinted pg_restore: creating TABLE tblempinctransfer pg_restore: creating TABLE tblempincudefq pg_restore: creating TABLE tblempincunsafeact pg_restore: creating TABLE tblempincunsafeboolean pg_restore: creating TABLE tblempincunsafecondition pg_restore: creating TABLE tblempincvehicleinjuredtype pg_restore: creating TABLE tblempincvehicleloss pg_restore: creating TABLE tblempincweatherconditions pg_restore: creating TABLE tblempincwitnesslog pg_restore: creating TABLE tblempincwitnesstype pg_restore: creating TABLE tblevidence pg_restore: creating TABLE tblevidencefolder pg_restore: creating TABLE tblevidenceimmediatealertemailsent pg_restore: creating TABLE tblevidenceorigtransref pg_restore: creating TABLE tblevidenceproblemcodes pg_restore: creating TABLE tblevidencetender pg_restore: creating TABLE tblevidencetrans pg_restore: creating TABLE tblevidencetranscustomer pg_restore: creating TABLE tblevidencetransline pg_restore: creating TABLE tblexceptionimportlog pg_restore: creating TABLE tblexceptionworkfolder pg_restore: creating TABLE tblexportclients pg_restore: creating TABLE tblexportenity pg_restore: creating TABLE tblexportentityxrefvalues pg_restore: creating TABLE tblexportfields_entity_master pg_restore: creating TABLE tblexportfields_waz_master pg_restore: creating TABLE tblexportfields_xref pg_restore: creating TABLE tblexporttables pg_restore: creating TABLE tbleyecolor pg_restore: creating TABLE tblfacmgmtadditionalcharges pg_restore: creating TABLE tblfacmgmtequipment pg_restore: creating TABLE tblfacmgmtjobstatus pg_restore: creating TABLE tblfacmgmtjobtype pg_restore: creating TABLE tblfacmgmtlabor pg_restore: creating TABLE tblfacmgmtmain pg_restore: creating TABLE tblfacmgmtmaterials pg_restore: creating TABLE tblfacmgmtreasonforcall pg_restore: creating TABLE tblfacmgmtservicecompany pg_restore: creating TABLE tblfacmgmtservicetechnician pg_restore: creating TABLE tblfacmgmtservicetype pg_restore: creating TABLE tblfacmgmttechniciancertificationlevel pg_restore: creating TABLE tblfacmgmtworkperformedtype pg_restore: creating TABLE tblfieldorder pg_restore: creating TABLE tblfmvehicle pg_restore: creating TABLE tblfmvehiclehoused pg_restore: creating TABLE tblfmvehiclenotes pg_restore: creating TABLE tblfmvehicleownership pg_restore: creating TABLE tblfmvehiclesafety pg_restore: creating TABLE tblfmvehiclesecurity pg_restore: creating TABLE tblftpconfig pg_restore: creating TABLE tblgeneralparam pg_restore: creating TABLE tblgeneralparambymodule pg_restore: creating TABLE tblgroup pg_restore: creating TABLE tblgroupaudit pg_restore: creating TABLE tblgroupcompanies pg_restore: creating TABLE tblgroupcompaniesavailable pg_restore: creating TABLE tblgrouplocations pg_restore: creating TABLE tblgroupnonparentusers pg_restore: creating TABLE tblgroupresponse_line pg_restore: creating TABLE tblgroupresponseheader pg_restore: creating TABLE tblgroupresponsesection pg_restore: creating TABLE tblgroupuser pg_restore: creating TABLE tblhaircolor pg_restore: creating TABLE tblholdtranfields pg_restore: creating TABLE tblhrdept pg_restore: creating TABLE tblimmediatealertemail pg_restore: creating TABLE tblimmediatealertemailsent pg_restore: creating TABLE tblimmediatealertreport pg_restore: creating TABLE tblincident pg_restore: creating TABLE tblincidentcallback pg_restore: creating TABLE tblincidentdept pg_restore: creating TABLE tblincidentperson pg_restore: creating TABLE tblincidentpersontitle pg_restore: creating TABLE tblincidentreported pg_restore: creating TABLE tblincidentsuppquest pg_restore: creating TABLE tblincidentsuppquestansw pg_restore: creating TABLE tblincidenttype pg_restore: creating TABLE tblinttendertype pg_restore: creating TABLE tblinttranslinetype pg_restore: creating TABLE tblinttranstype pg_restore: creating TABLE tblinvestigation pg_restore: creating TABLE tbllevel pg_restore: creating TABLE tbllocationtype pg_restore: creating TABLE tbllpregion pg_restore: creating TABLE tbllptools pg_restore: creating TABLE tblmaritalstatus pg_restore: creating TABLE tblmarkettype pg_restore: creating TABLE tblmodule pg_restore: creating TABLE tblmonthendreport pg_restore: creating TABLE tblnextreportdefid pg_restore: creating TABLE tblnote pg_restore: creating TABLE tbloffensecode pg_restore: creating TABLE tbloldpasswords pg_restore: creating TABLE tblorigtransref pg_restore: creating TABLE tblpassincounter pg_restore: creating TABLE tblportalcontent pg_restore: creating TABLE tblportalpermissions pg_restore: creating TABLE tblportaltype pg_restore: creating TABLE tblpostvoidreasoncode pg_restore: creating TABLE tblpresentationlayers pg_restore: creating TABLE tblproblemcodeconfig pg_restore: creating TABLE tblproblemcodemast pg_restore: creating TABLE tblquerrydefinefields pg_restore: creating TABLE tblquerrydefineheader pg_restore: creating TABLE tblquestions pg_restore: creating TABLE tblrefundreasoncode pg_restore: creating TABLE tblreportdef pg_restore: creating TABLE tblreportexecution pg_restore: creating TABLE tblreportqueue pg_restore: creating TABLE tblreporttype pg_restore: creating TABLE tblresponse_line pg_restore: creating TABLE tblresponseheader pg_restore: creating TABLE tblresponsesection pg_restore: creating TABLE tblreturnreasoncode pg_restore: creating TABLE tblrtwactivity pg_restore: creating TABLE tblrtwcapabilities pg_restore: creating TABLE tblrtwfrequency pg_restore: creating TABLE tblrtwhours pg_restore: creating TABLE tblrtwjanalysis pg_restore: creating TABLE tblrtwprocedures pg_restore: creating TABLE tblsafcustreviewedaccidents pg_restore: creating TABLE tblsafempreviewedaccidents pg_restore: creating TABLE tblsafmeet pg_restore: creating TABLE tblsafmeetareaconcern pg_restore: creating TABLE tblsafmeetattlog pg_restore: creating TABLE tblsafmeetconcerns pg_restore: creating TABLE tblsafmeetmessageofmonth pg_restore: creating TABLE tblsafmeetmethodcommunicated pg_restore: creating TABLE tblsafmeetnewbusiness pg_restore: creating TABLE tblsafmeetnewbusinesstype pg_restore: creating TABLE tblsearsautoxref pg_restore: creating TABLE tblsections pg_restore: creating TABLE tblshk pg_restore: creating TABLE tblshkdepartmentshrinkhistory pg_restore: creating TABLE tblshkstoreshrinkhistory pg_restore: creating TABLE tblsiccodes pg_restore: creating TABLE tblstore pg_restore: creating TABLE tblsubdepartment pg_restore: creating TABLE tblsubjectbymodule pg_restore: creating TABLE tblsuspectedactivity pg_restore: creating TABLE tbltender pg_restore: creating TABLE tbltendertype pg_restore: creating TABLE tbltesttype pg_restore: creating TABLE tbltnwxref pg_restore: creating TABLE tbltranscustomer pg_restore: creating TABLE tbltranscustomer_bom pg_restore: creating TABLE tbltranslationfields pg_restore: creating TABLE tbltranslations pg_restore: creating TABLE tbltransline pg_restore: creating TABLE tbltranslinetype pg_restore: creating TABLE tbltranslinetype_bom pg_restore: creating TABLE tbltranstype pg_restore: creating TABLE tbltypeofweapon pg_restore: creating TABLE tblusagestats pg_restore: creating TABLE tblusermovementlog pg_restore: creating TABLE tblusstates pg_restore: creating TABLE tblvehiclebodytype pg_restore: creating TABLE tblvehiclemake pg_restore: creating TABLE tblvehiclepolicylimit pg_restore: creating TABLE tblvehiclepolicytype pg_restore: creating TABLE tblvendor pg_restore: creating TABLE tblweekday pg_restore: creating TABLE tblworkflowtypes pg_restore: creating TABLE tblyeardesignation pg_restore: creating VIEW thenetworkcallbackview pg_restore: creating VIEW thenetworkview pg_restore: creating VIEW username_duppes pg_restore: creating VIEW viewallfields pg_restore: creating VIEW viw_aigc_groupusers pg_restore: creating VIEW viw_assoc_export pg_restore: creating VIEW viw_dropdowntables pg_restore: creating VIEW viw_duplicate_case_numbers pg_restore: creating VIEW viw_duplicate_case_numbers2 pg_restore: creating VIEW viw_emailtotalbyemailinpriortosend pg_restore: creating VIEW viw_exportcase_noaprhdate pg_restore: creating VIEW viw_locations_too_high pg_restore: creating VIEW viw_long_cases_export pg_restore: creating VIEW viw_longs_special pg_restore: creating VIEW viw_rnd_locs pg_restore: creating VIEW viwassoclist pg_restore: creating VIEW viwauditcube pg_restore: creating VIEW viwauditcubeavg pg_restore: creating VIEW viwauditfeedbacklist pg_restore: creating VIEW viwauditlist pg_restore: creating VIEW viwawardcube pg_restore: creating VIEW viwawardquery pg_restore: creating VIEW viwcase_wcasereport pg_restore: creating VIEW viwcase_wcasereport_assoc pg_restore: creating VIEW viwcase_wcasereport_other pg_restore: creating VIEW viwcasecube pg_restore: creating VIEW viwcasecube_export_ent pg_restore: creating VIEW viwcasecube_w_auto_as_main pg_restore: creating VIEW viwcasecubetools pg_restore: creating VIEW viwcaseexp_with_tools pg_restore: creating VIEW viwcaseexport_small_case_rep_4096 pg_restore: creating VIEW viwcaseexportnoappdate pg_restore: creating VIEW viwoldandnewcivil pg_restore: creating VIEW viwcaselist pg_restore: creating VIEW viwcaserestitutioncube pg_restore: creating VIEW viwcdacube pg_restore: creating VIEW viwcdapayments pg_restore: creating VIEW viwcoilist pg_restore: creating VIEW viwcreateaudit pg_restore: creating VIEW viwcustinccube pg_restore: creating VIEW viwcustinclist pg_restore: creating VIEW viwdotseal pg_restore: creating VIEW viwdotsealcube pg_restore: creating VIEW viweicube pg_restore: creating VIEW viweilist pg_restore: creating VIEW viwempinccube pg_restore: creating VIEW viwempinccubeuserdefinquest pg_restore: creating VIEW viwempinclist pg_restore: creating VIEW viwevidencelist pg_restore: creating VIEW viwexportentitylist pg_restore: creating VIEW viwexportlist pg_restore: creating VIEW viwfacmgmt pg_restore: creating VIEW viwfacmgmtservicecompany pg_restore: creating VIEW viwgicube pg_restore: creating VIEW viwgilist pg_restore: creating VIEW viwgroupauditcube pg_restore: creating VIEW viwgroupauditlist pg_restore: creating VIEW viwillnesscount pg_restore: creating VIEW viwincidentcube pg_restore: creating VIEW viwincidentlist pg_restore: creating VIEW viwlocationasis pg_restore: creating VIEW viwlocationwdivregdis pg_restore: creating VIEW viwlocxref pg_restore: WARNING: column "deletecrossreference" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore: creating VIEW viwmerchcube pg_restore: creating VIEW viwmovementlog pg_restore: creating VIEW viwpriors pg_restore: creating VIEW viwproblemcodes pg_restore: creating VIEW viwrtwcap pg_restore: creating VIEW viwsafmeet pg_restore: creating VIEW viwsdotsealcube pg_restore: creating VIEW viwsflp_casevalue_vs_paid pg_restore: creating VIEW viwshrink pg_restore: creating VIEW viwsnapcount pg_restore: creating VIEW viwsnapmerch pg_restore: creating VIEW viwsnapshot pg_restore: creating VIEW viwtendercube pg_restore: creating VIEW viwtnwincident pg_restore: creating VIEW viwusma_count pg_restore: restoring data for table "tbl_i2an_default_values" pg_restore: restoring data for table "tblaction" pg_restore: ERROR: invalid byte sequence for encoding "UNICODE": 0xe9 CONTEXT: COPY tblaction, line 1799, column value: "Chargé" pg_restore: [archiver (db)] error returned by PQendcopy pg_restore: *** aborted because of error Process returned exit code 1. Joel Fradkin
Joel Fradkin wrote: > Can anyone help me out on this (I will need to backup and restore the data > base, but am not savy on the proper save and restore syntax). I used the > default ones in PGadmin as detailed below and it blew up on > pg_restore: restoring data for table "tblaction" > pg_restore: ERROR: invalid byte sequence for encoding "UNICODE": 0xe9 > CONTEXT: COPY tblaction, line 1799, column value: "Chargé" > pg_restore: [archiver (db)] error returned by PQendcopy > pg_restore: *** aborted because of error Looking at your output, that's not the only error. > Backup command= > D:\Program Files\pgAdmin III\pg_dump.exe -i -h 192.168.123.112 -p 5432 -U > postgres -F c -b -v -f "D:\backups\postgrescompressed.backup" wazagua > > Restorecommand= > D:\Program Files\pgAdmin III\pg_restore.exe -i -h 192.168.123.112 -p 5432 -U > postgres -d waztest -v "D:\backups\postgrescompressed.backup" > > Using > ServerVersion: 07.03.0200 PostgreSQL 7.4.6 on i386-redhat-linux-gnu, > compiled by GCC i386-redhat-linux-gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2 Good to see all the information needed - means we don't need to make any guesses. > Resulting error text = > D:\Program Files\pgAdmin III\pg_restore.exe -i -h 192.168.123.112 -p 5432 -U > postgres -d waztest -v "D:\backups\postgrescompressed.backup" > pg_restore: connecting to database for restore > pg_restore: creating DUMP TIMESTAMP DUMP TIMESTAMP > pg_restore: creating SCHEMA public > pg_restore: creating COMMENT SCHEMA public > pg_restore: creating FUNCTION plpgsql_call_handler() > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 9; 1255 16207920 FUNCTION > plpgsql_call_handler() postgres > pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at > or near "OWNER" at character 46 > Command was: ALTER FUNCTION public.plpgsql_call_handler() OWNER TO > postgres; OK - let's start here. The syntax here is fine, I just tested it on an 8.0 database. Are you sure the DB you are restoring to is version 8? > pg_restore: creating PROCEDURAL LANGUAGE plpgsql > pg_restore: creating FUNCTION inserttabledata(character varying, character > varying) > pg_restore: [archiver (db)] Error from TOC entry 10; 1255 16227934 FUNCTION > inserttabledata(character varying, character varying) postgres > pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at > or near "$" at character 94 > Command was: CREATE FUNCTION inserttabledata(character varying, > character varying) RETURNS boolean > AS $_$ This is odd too - another version 8 feature. I've skipped to the end here: > pg_restore: restoring data for table "tblaction" > pg_restore: ERROR: invalid byte sequence for encoding "UNICODE": 0xe9 > CONTEXT: COPY tblaction, line 1799, column value: "Chargé" Well, it's complaining that the data you're restoring isn't unicode (UTF-8). Was the database you dumped from set up to store UTF-8 or was it SQL-ASCII or ISOxxxx? I suppose it might be something to do with a linux<=>windows transfer, but the place to start is running "psql -l" on the server. -- Richard Huxton Archonet Ltd
Richard Huxton <dev@archonet.com> writes:
> Joel Fradkin wrote:
>> ServerVersion: 07.03.0200 PostgreSQL 7.4.6 on i386-redhat-linux-gnu,
>> compiled by GCC i386-redhat-linux-gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2
> OK - let's start here. The syntax here is fine, I just tested it on an
> 8.0 database. Are you sure the DB you are restoring to is version 8?
Looks to me like he's trying to use 8.0 pg_dump/pg_restore to reload
into a 7.4 server. This definitely won't work without specifying
--disable-dollar-quoting to pg_dump; and if you care about restoring
object ownership correctly, also --use-set-session-authorization.
I don't recall if there are any other gotchas.
The unicode issue I'm not sure about. Perhaps the original database was
SQL_ASCII encoding and so was allowed to contain byte sequences that
aren't legal unicode?
regards, tom lane
I am not running version 8 (I did try this on the linux box as well, but the
version I documented was the server is linux and the client is XP).
I ran it from PG admin in both cases, maybe I need to just run from the
command line on the linux box (this is fine as long as I can backup the file
and restore it if need be). I did the dump and restore from the PGadminIII
program so maybe the data base is not (UTF-8). I am new to this so I do not
know how to determine and set the types so they match up.
I did a create database and am running a .net app to read the data from
MSSQL and add it to Postgres.
If anyone know a quick to determine this and what the syntax mods are for
the dump and restore I would be very happy, if not I can play around. Thanks
so much for all the help, maybe I should load version 8 (I was not sure
there were rpms for fedora 3 that worked, I tried to load it and was
obviously not doing it correctly).
Joel Fradkin
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel. 941-753-7111 ext 305
jfradkin@wazagua.com
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, IncThis email message is for the use of the intended recipient(s)
andmay
contain confidential and privileged information. Any unauthorized review,
use, disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete and destroy
all copies of the original message, including attachments.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, February 03, 2005 1:17 PM
To: Richard Huxton
Cc: Joel Fradkin; pgsql-sql@postgresql.org
Subject: Re: [SQL] problem with backup and restore (probaly stupit newb
thing)
Richard Huxton <dev@archonet.com> writes:
> Joel Fradkin wrote:
>> ServerVersion: 07.03.0200 PostgreSQL 7.4.6 on i386-redhat-linux-gnu,
>> compiled by GCC i386-redhat-linux-gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2
> OK - let's start here. The syntax here is fine, I just tested it on an
> 8.0 database. Are you sure the DB you are restoring to is version 8?
Looks to me like he's trying to use 8.0 pg_dump/pg_restore to reload
into a 7.4 server. This definitely won't work without specifying
--disable-dollar-quoting to pg_dump; and if you care about restoring
object ownership correctly, also --use-set-session-authorization.
I don't recall if there are any other gotchas.
The unicode issue I'm not sure about. Perhaps the original database was
SQL_ASCII encoding and so was allowed to contain byte sequences that
aren't legal unicode?
regards, tom lane