Re: restore database errors

[Charley Tiggs <..hidden..>]

Two ways to handle the errors:

1) on your dump, include another parameter (-O) so that ownership is not 
assigned:

pg_dump -h 192.168.0.202 -O -U pgsql computerking > dumpfile.txt

The trouble with this is that ownership will be assigned to the user 
that you use to import the database.  If you're like me, the user you 
created to use with LSMB doesn't have super user privileges so it can't 
handle creating the plpgsql language.  So, you'll need to go in and 
change permissions for each object after import, which can be tedious. 
The leads me to alternative #2...

2) open the dump file and replace every instance of role "sql-ledger" 
with the appropriate user

After you've done one of the two above, import the new dump file with:

psql yourdb SUPERUSER -f dumpfile.txt

You must use a superuser for the import.  If you do number two and 
you've already created the user with the proper permissions, then your 
objects will be assigned to the appropriate user on import.

And you're correct, proper syntax is psql [DBNAME] [USERNAME]. Apologies 
for getting that mixed up.

Charley

RYAN M. vAN GINNEKEN wrote:
> Thanks for you reply Charley or should i call you Mr. Tiggs i entered the commands as you suggest from the new server i ran this looks good.
>
> ..hidden.. /home/c0mputerking]# pg_dump -h 192.168.0.202 -U pgsql computerking > dumpfile.txt
> ..hidden.. /home/c0mputerking]#
>
> Again on the machine with the new server i ran this and got the below for an output some error and a question by running the below command am i not loading back into the postgres database should i be using the newly created database ck1 that i created for my user account on ledgersmb I am confused and i think the syntax for psql is username then database not the way you stated in your post.
>
>
>
> ..hidden.. /home/c0mputerking]# psql postgres pgsql -f dumpfile.txt
> SET
> SET
> SET
> SET
> SET
> COMMENT
> SET
> CREATE FUNCTION
> psql:dumpfile.txt:29: ERROR:  role "rmvg" does not exist
> CREATE LANGUAGE
> CREATE FUNCTION
> psql:dumpfile.txt:69: ERROR:  role "sql-ledger" does not exist
> CREATE FUNCTION
> psql:dumpfile.txt:96: ERROR:  role "sql-ledger" does not exist
> CREATE FUNCTION
> psql:dumpfile.txt:122: ERROR:  role "sql-ledger" does not exist
> CREATE FUNCTION
> psql:dumpfile.txt:140: ERROR:  role "sql-ledger" does not exist
> CREATE FUNCTION
> psql:dumpfile.txt:156: ERROR:  role "sql-ledger" does not exist
> CREATE FUNCTION
> psql:dumpfile.txt:207: ERROR:  role "sql-ledger" does not exist
> CREATE FUNCTION
> psql:dumpfile.txt:225: ERROR:  role "sql-ledger" does not exist
> CREATE FUNCTION
> psql:dumpfile.txt:243: ERROR:  role "sql-ledger" does not exist
> CREATE FUNCTION
> psql:dumpfile.txt:259: ERROR:  role "sql-ledger" does not exist
> CREATE FUNCTION
> psql:dumpfile.txt:285: ERROR:  role "sql-ledger" does not exist
> SET
> SET
> CREATE TABLE
> psql:dumpfile.txt:309: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:343: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:377: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:392: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:409: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:422: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:440: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:481: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:493: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:528: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:541: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:553: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:586: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:600: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:612: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:629: ERROR:  role "sql-ledger" does not exist
> CREATE SEQUENCE
> psql:dumpfile.txt:642: ERROR:  role "sql-ledger" does not exist
>  setval
> --------
>   10220
> (1 row)
>
> CREATE TABLE
> psql:dumpfile.txt:666: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:691: ERROR:  role "sql-ledger" does not exist
> CREATE SEQUENCE
> psql:dumpfile.txt:704: ERROR:  role "sql-ledger" does not exist
>  setval
> --------
>     265
> (1 row)
>
> CREATE TABLE
> psql:dumpfile.txt:734: ERROR:  role "sql-ledger" does not exist
> CREATE SEQUENCE
> psql:dumpfile.txt:747: ERROR:  role "sql-ledger" does not exist
>  setval
> --------
>       1
> (1 row)
>
> CREATE TABLE
> psql:dumpfile.txt:766: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:779: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:811: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:834: ERROR:  role "sql-ledger" does not exist
> CREATE SEQUENCE
> psql:dumpfile.txt:847: ERROR:  role "sql-ledger" does not exist
>  setval
> --------
>      17
> (1 row)
>
> CREATE TABLE
> psql:dumpfile.txt:891: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:909: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:921: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:933: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:949: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:961: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:980: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:999: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:1013: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:1027: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:1049: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:1062: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:1077: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:1091: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:1104: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:1146: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:1158: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:1170: ERROR:  role "sql-ledger" does not exist
> CREATE TABLE
> psql:dumpfile.txt:1182: ERROR:  role "sql-ledger" does not exist
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE TRIGGER
> CREATE TRIGGER
> CREATE TRIGGER
> CREATE TRIGGER
> CREATE TRIGGER
> CREATE TRIGGER
> CREATE TRIGGER
> CREATE TRIGGER
> CREATE TRIGGER
> CREATE TRIGGER
> CREATE TRIGGER
> CREATE TRIGGER
> CREATE TRIGGER
> CREATE TRIGGER
> CREATE TRIGGER
> CREATE TRIGGER
> CREATE TRIGGER
> CREATE TRIGGER
> REVOKE
> GRANT
> ..hidden.. /home/
> ----- Charley Tiggs <..hidden..> wrote:
> > Can the new server access the old server?  If so, you should use
> > pg_dump 
> > from 8.2.3 to dump the file instead (I think that's possible).  Doing
> > so 
> > means that the dump will be output in a format that can be read 
> > correctly by psql 8.2.3 on import.
> >
> > I'm not a windows user but format of the command should be pretty 
> > similar from the command-line:
> >
> > pg_dump -h IP_OF_OLD_SERVER -U ADMINUSERNAME DB_NAME > dumpfile.txt
> >
> > to pull the file back in:
> >
> > psql ADMINUSERNAME postgres -f dumpfile.txt
> >
> > I'm not familiar enough with pgadminIII to advise you on how to
> > approach 
> > this from within that GUI interface.
> >
> > Charley
> >
> > RYAN M. vAN GINNEKEN wrote:
> > > hmm no one on this list able to help?? where should i be posting
> > these questions as stated on your website i was expecting some
> > friendly support 
> > > ----- Original Message ----- 
> > > From: RYAN M. vAN GINNEKEN <..hidden..> 
> > > To: ..hidden.. 
> > > Sent: Wednesday, April 18, 2007 2:26:44 PM GMT-0700 
> > > Subject: [Ledger-smb-users] restore database errors 
> > >
> > >
> > >
> > >
> > > yes i am running pg-dump on window i do not really like dealing with
> > databases so i am using pgadminIII a gui connected to my databases
> > make life much easier 
> > > sorry i sent incomplete text attachment last time my attachment
> > keeps getting bounced so i have attached a zip file with the extension
> > txt 
> > > I get lots of errors when i try to restore my database for
> > sql-ledger to the shiny new smbledger I have attached them hope this
> > list allows attachment. the dump goes fine but the restore seem very
> > bad here are the versions involed 
> > > old server 
> > > sql-ledger-2.6.25 
> > > postgresql-server-7.4.13_1 
> > >
> > > new server 
> > > ledgersmb 1.2.3 
> > > postgresql-server-8.2.3 
> > ------------------------------------------------------------------------
> > >
> > -------------------------------------------------------------------------
> > > This SF.net email is sponsored by DB2 Express
> > > Download DB2 Express C - the FREE version of DB2 express and take
> > > control of your XML. No limits. Just data. Click to get it now.
> > > http://sourceforge.net/powerbar/db2/
> > ------------------------------------------------------------------------
> > > _______________________________________________
> > > Ledger-smb-users mailing list
> > > ..hidden..
> > > https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
> >
> > -------------------------------------------------------------------------
> > This SF.net email is sponsored by DB2 Express
> > Download DB2 Express C - the FREE version of DB2 express and take
> > control of your XML. No limits. Just data. Click to get it now.
> > http://sourceforge.net/powerbar/db2/
> > _______________________________________________
> > Ledger-smb-users mailing list
> > ..hidden..
> > https://lists.sourceforge.net/lists/listinfo/ledger-smb-users