Quickest command to dump and restore the database? : pg_dump andpg_restore? using PostgreSQL 8.3 ru

--_000_D78A8169F9436B4DB978300336168F3B3359E501D3SWBREXCH00u cs_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi all


I would like to know the quickest way of dumping and restoring the database=
in version 8.3 of PostgreSQL database running in RedHat 5.3.

We are currently using the command below, which seems to be very slow. We a=
re moving from individual database to centralized database


pg_dump -U enterprisedb -Fc -t ${i} --data-only --ignore-version ${STORE_DB=
_HOST} -p ${DB_PORT} ${STORE_DB} | ${PG_BIN}/pg_restore -U enterprisedb -p =
${DB_PORT} -t ${i} --data-only --dbname=3D${CDBS_DB}



Please assist...Thanks in advance





Thanks








Confidentiality Notice:http://ucs.co.za/conf.html



________________________________
The contents of and attachments to this e-mail are intended for the address=
ee only, and may contain the confidential information of UCS Group and/or i=
ts subsidiaries. Any review, use or dissemination thereof by anyone other t=
han the intended addressee is prohibited. If you are not the intended addre=
ssee please notify the writer immediately and destroy the e-mail. UCS Group=
Limited and its subsidiaries distance themselves from and accept no liabil=
ity for unauthorised use of their e-mail facilities or e-mails sent other t=
han strictly for business purposes.

--_000_D78A8169F9436B4DB978300336168F3B3359E501D3SWBREXCH00u cs_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" xmlns:p=3D"urn:schemas-m=
icrosoft-com:office:powerpoint" xmlns:a=3D"urn:schemas-microsoft-com:office=
:access" xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s=3D"=
uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs=3D"urn:schemas-microsof=
t-com:rowset" xmlns:z=3D"#RowsetSchema" xmlns:b=3D"urn:schemas-microsoft-co=
m:office:publisher" xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadshee=
t" xmlns:c=3D"urn:schemas-microsoft-com:office:component:spread sheet" xmlns=
:odc=3D"urn:schemas-microsoft-com:office:odc" xmlns:oa=3D"urn:schemas-micro=
soft-com:office:activation" xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc=3D"http://m=
icrosoft.com/officenet/conferencing" xmlns:D=3D"DAV:" xmlns:Repl=3D"http://=
schemas.microsoft.com/repl/" xmlns:mt=3D"http://schemas.microsoft.com/share=
point/soap/meetings/" xmlns:x2=3D"http://schemas.microsoft.com/office/excel=
/2003/xml" xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" xmlns:ois=
=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir=3D"http://=
schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds=3D"http://www.w3=
..org/2000/09/xmldsig#" xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint=
/dsp" xmlns:udc=3D"http://schemas.microsoft.com/data/udc" xmlns:xsd=3D"http=
://www.w3.org/2001/XMLSchema" xmlns:sub=3D"http://schemas.microsoft.com/sha=
repoint/soap/2002/1/alerts/" xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#"=
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" xmlns:sps=3D"http://=
schemas.microsoft.com/sharepoint/soap/" xmlns:xsi=3D"http://www.w3.org/2001=
/XMLSchema-instance" xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/so=
ap" xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile " xmlns:udc=
p2p=3D"http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf=3D"http:/=
/schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss=3D"http://sche=
mas.microsoft.com/office/2006/digsig-setup" xmlns:dssi=3D"http://schemas.mi=
crosoft.com/office/2006/digsig" xmlns:mdssi=3D"http://schemas.openxmlformat=
s.org/package/2006/digital-signature" xmlns:mver=3D"http://schemas.openxmlf=
ormats.org/markup-compatibility/2006" xmlns:m=3D"http://schemas.microsoft.c=
om/office/2004/12/omml" xmlns:mrels=3D"http://schemas.openxmlformats.org/pa=
ckage/2006/relationships" xmlns:spwp=3D"http://microsoft.com/sharepoint/web=
partpages" xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/service s/20=
06/types" xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/service s/200=
6/messages" xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/ Sli=
deLibrary/" xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPor tal=
Server/PublishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" xmlns:=
st=3D"
" xmlns=3D"http://www.w3.org/TR/REC-html40">

>

Re: Quickest command to dump and restore the database? :

pg_dump and restore are typically fast. Have you tried other -F
values? c does compression which, in your case, on your servers, might
be slow. You could also try running the dump on the server that is
hosting the source database, copying the file across and loading it on
the target server. Or some variation on that theme. Maybe that would
at least tell you which bit was slow.


--
Ian.



On Tue, May 25, 2010 at 9:15 AM, Khangelani Gama
wrote:
> Hi all
>
>
>
>
>
> I would like to know the quickest way of dumping and restoring the databa=
se
> in version 8.3 of PostgreSQL database running in RedHat 5.3.
>
>
>
> We are currently using the command below, which seems to be very slow. We
> are moving from individual database to centralized database
>
>
>
>
>
> pg_dump -U enterprisedb -Fc -t ${i} --data-only --ignore-version
> ${STORE_DB_HOST} -p ${DB_PORT} ${STORE_DB} | ${PG_BIN}/pg_restore -U
> enterprisedb -p ${DB_PORT} -t ${i} --data-only --dbname=3D${CDBS_DB}
>
>
>
>
>
>
>
> Please assist=85Thanks in advance
>
>
>
>
>
>
>
>
>
>
>
> Thanks
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Confidentiality Notice:http://ucs.co.za/conf.html
>
>
>
>
>
> ________________________________
> The contents of and attachments to this e-mail are intended for the
> addressee only, and may contain the confidential information of UCS Group
> and/or its subsidiaries. Any review, use or dissemination thereof by anyo=
ne
> other than the intended addressee is prohibited. If you are not the inten=
ded
> addressee please notify the writer immediately and destroy the e-mail. UCS
> Group Limited and its subsidiaries distance themselves from and accept no
> liability for unauthorised use of their e-mail facilities or e-mails sent
> other than strictly for business purposes.
>

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Quickest command to dump and restore the database? : pg_dumpand pg_restore? using PostgreSQL 8.

--_000_68B59BEDCD36854AADBDF17E91B2937A01A57CCDF4EXCHMAILsta ff_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Try this:

pg_dump -a source_db | psql target_db

Sam


________________________________
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql=
..org] On Behalf Of Khangelani Gama
Sent: Tuesday, 25 May 2010 5:45 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Quickest command to dump and restore the database? : pg_du=
mp and pg_restore? using PostgreSQL 8.3 running on RedHat 5.3

Hi all


I would like to know the quickest way of dumping and restoring the database=
in version 8.3 of PostgreSQL database running in RedHat 5.3.

We are currently using the command below, which seems to be very slow. We a=
re moving from individual database to centralized database


pg_dump -U enterprisedb -Fc -t ${i} --data-only --ignore-version ${STORE_DB=
_HOST} -p ${DB_PORT} ${STORE_DB} | ${PG_BIN}/pg_restore -U enterprisedb -p =
${DB_PORT} -t ${i} --data-only --dbname=3D${CDBS_DB}



Please assist...Thanks in advance





Thanks








Confidentiality Notice:http://ucs.co.za/conf.html



________________________________
The contents of and attachments to this e-mail are intended for the address=
ee only, and may contain the confidential information of UCS Group and/or i=
ts subsidiaries. Any review, use or dissemination thereof by anyone other t=
han the intended addressee is prohibited. If you are not the intended addre=
ssee please notify the writer immediately and destroy the e-mail. UCS Group=
Limited and its subsidiaries distance themselves from and accept no liabil=
ity for unauthorised use of their e-mail facilities or e-mails sent other t=
han strictly for business purposes.

--_000_68B59BEDCD36854AADBDF17E91B2937A01A57CCDF4EXCHMAILsta ff_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" xmlns:p=3D"urn:schemas-m=
icrosoft-com:office:powerpoint" xmlns:a=3D"urn:schemas-microsoft-com:office=
:access" xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s=3D"=
uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs=3D"urn:schemas-microsof=
t-com:rowset" xmlns:z=3D"#RowsetSchema" xmlns:b=3D"urn:schemas-microsoft-co=
m:office:publisher" xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadshee=
t" xmlns:c=3D"urn:schemas-microsoft-com:office:component:spread sheet" xmlns=
:odc=3D"urn:schemas-microsoft-com:office:odc" xmlns:oa=3D"urn:schemas-micro=
soft-com:office:activation" xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc=3D"http://m=
icrosoft.com/officenet/conferencing" xmlns:D=3D"DAV:" xmlns:Repl=3D"http://=
schemas.microsoft.com/repl/" xmlns:mt=3D"http://schemas.microsoft.com/share=
point/soap/meetings/" xmlns:x2=3D"http://schemas.microsoft.com/office/excel=
/2003/xml" xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" xmlns:ois=
=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir=3D"http://=
schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds=3D"http://www.w3=
..org/2000/09/xmldsig#" xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint=
/dsp" xmlns:udc=3D"http://schemas.microsoft.com/data/udc" xmlns:xsd=3D"http=
://www.w3.org/2001/XMLSchema" xmlns:sub=3D"http://schemas.microsoft.com/sha=
repoint/soap/2002/1/alerts/" xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#"=
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" xmlns:sps=3D"http://=
schemas.microsoft.com/sharepoint/soap/" xmlns:xsi=3D"http://www.w3.org/2001=
/XMLSchema-instance" xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/so=
ap" xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile " xmlns:udc=
p2p=3D"http://schemas.microsoft.com/data/udc/parttopart" xmlns:st=3D"
" =
xmlns:st1=3D"urn:schemas-microsoft-com:office:smarttags" xmlns=3D"http://ww=
w.w3.org/TR/REC-html40"
xmlns:ns0=3D"http://schemas.microsoft.com/sharepoint/soap/wo rkflow/"
xmlns:ns1=3D"http://schemas.microsoft.com/office/2006/digsig -setup"
xmlns:ns2=3D"http://schemas.microsoft.com/office/2006/digsig "
xmlns:ns3=3D"http://schemas.openxmlformats.org/package/2006/ digital-signatu=
re"
xmlns:ns4=3D"http://schemas.openxmlformats.org/markup-compat ibility/2006"
xmlns:ns5=3D"http://schemas.microsoft.com/office/2004/12/omm l"
xmlns:ns6=3D"http://schemas.openxmlformats.org/package/2006/ relationships"
xmlns:ns7=3D"http://microsoft.com/sharepoint/webpartpages"
xmlns:ns8=3D"http://schemas.microsoft.com/exchange/services/ 2006/types"
xmlns:ns9=3D"http://schemas.microsoft.com/exchange/services/ 2006/messages"
xmlns:ns10=3D"http://schemas.microsoft.com/sharepoint/soap/S lideLibrary/"
xmlns:ns11=3D"http://microsoft.com/webservices/SharePointPor talServer/Publi=
shedLinksService"
xmlns:ns12=3D"urn:schemas-microsoft-com:">


>

namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags" name=3D"Person=
Name"/>