--0003255584f227dcf104721a3324
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Hi Andy,
First of all, thank you very much for your advices,
2009/8/26 Andy Shellam <andy-lists [at] networkmail.eu>
> Hi Pablo
>
> - As regards the duplicated rows, no, I don't get duplicated rows in all
>> the tables stored in the database because
>> some tables have primary-keys (and/or UNIQUE) constraints. These
>> constraints don't allow the restore process to
>> duplicate rows. In fact, it is a kind of "solution" I've tried...(add an
>> extra column with a primary-key or unique constraint, to this tables), and
>> it "works". The restore process doesn't generate duplicated rows, because
>> the constraint does not allow the insertion
>> of new duplicated data. Anyway..it looks like a kind of 'poor solution'
>> :-)
>>
>
> Yeah, that's one solution, only trouble being if the data in the existing
> table is different to what's in the restore script (for a record with the
> same ID) it won't be updated.
>
> e.g.: in this example your restored database will be inconsistent with the
> backup.
> your table: Field1 = 1 (ID), Field2 = A, Field3 = B
> restore script: Field1 = 1 (ID), Field2 = B, Field3 = B
>
Yes!, you're right, that could be a serious problem...some modified data
will keep the same and not restored to the status it had
when the backup was made. Damn...
I've tought about a "heavy-method" solution.....make an "dumb-proof" utility
to backup and restore the 'data' directory of the PostgresSQL server. It is
not considered as good as a SQL dump, because it requires to shutdown the
server, but that's not
a problem for me.
>
> - Ok, thanks for the info, I thought pg_dumpall would work as I desired
> even on non-empty clusters.
> Of course...if there is no previous data, the restore process will never
> create duplicated rows.
>
Exactly. If you're looking for some form of replication (i.e.
> master-to-slave) look at Slony - it fires triggers on the master that insert
> data into the slave. It has its limitations but AFAIK it's a workable
> solution.
>
No, I was not looking for a replication system. Just a "simple" method to
backup and restore data for users with very very low knowledge about
computers administration and zero knowledge about postgreSQL. I'll not be
able to spend much time with the users, so they should be able to make
administration tasks without much supervision, and I was looking for a kind
of "fire-and-forget" procedure Something they could do without many details
to worry about.
>
> - Yes, the restore process generates errors, because it tries to
> re-generate data structures that exist in the database server
> at that moment. Even if I delete my own databases, some errors will appear
> (because I cannot delete the internal stuff of
> of the server -> the 'postgres' database for example) . Those errors, could
> be ignored in most cases I think, but perhaps create a kind of "bad feeling"
> about the result of the restore process, or can "hide" other more important
> errors when you get a huge ammount of info on the screen either.
>
You can delete the "postgres" database - it's an empty database that's
> created when the server is initialised so you've got something to connect
> to. It's safe to delete, as long as you have another database you can
> connect to, but there's no real reason to unless it's in your restore script
> (e.g. from pg_dumpall.)
>
> http://www.postgresql.org/docs/8.3/static/manage-ag-template dbs.html
>
> Regards,
> Andy
>
Ah, ok, I thought it was NOT possible to delete postgres because I was not
allowed to do it from pgAdmin.
Thanks again,
Regards Pablo
--0003255584f227dcf104721a3324
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Hi Andy,<br><br>First of all, thank you very much for your advices,<br><br>=
<div class=3D"gmail_quote">2009/8/26 Andy Shellam <span dir=3D"ltr"><and=
y-lists [at] networkmail.eu></span><br><blockquote class=3D"gmail_quote" styl=
e=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; =
padding-left: 1ex;">
Hi Pablo<div class=3D"im"><br>
<br>
<blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, =
204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
- As regards the duplicated rows, no, I don't get duplicated rows in al=
l the tables stored in the database because<br>
some tables have primary-keys (and/or UNIQUE) constraints. These constraint=
s don't allow the restore process to<br>
duplicate rows. =A0In fact, it is a kind of "solution" I've t=
ried...(add an extra column with a primary-key or unique constraint, to thi=
s tables), and it "works". The restore process doesn't genera=
te duplicated rows, because the constraint does not allow the insertion<br>
of new duplicated data. =A0Anyway..it looks like a kind of 'poor soluti=
on' =A0:-)<br>
</blockquote>
<br></div>
Yeah, that's one solution, only trouble being if the data in the existi=
ng table is different to what's in the restore script (for a record wit=
h the same ID) it won't be updated.<br>
<br>
e.g.: in this example your restored database will be inconsistent with the =
backup.<br>
your table: Field1 =3D 1 (ID), Field2 =3D A, Field3 =3D B<br>
restore script: Field1 =3D 1 (ID), Field2 =3D B, Field3 =3D B<div class=3D"=
im"></div></blockquote><div>=A0</div><div class=3D"im">Yes!, you're rig=
ht, that could be a serious problem...some modified data will keep the same=
and not restored to the status it had<br>
when the backup was made. Damn...<br><br>I've tought about a "heav=
y-method" solution.....make an "dumb-proof" utility to backu=
p and restore the 'data' directory of the PostgresSQL server. It is=
not considered as good as a SQL dump, because it requires to shutdown the =
server, but that's not<br>
a problem for me.<br>=A0<br>
<blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, =
204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br>
- Ok, thanks for the info, I thought pg_dumpall would work as I desired eve=
n on non-empty clusters.<br>
Of course...if there is no previous data, the restore process will never cr=
eate duplicated rows.<br>
</blockquote>
<br></div><blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid=
rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Exactly. =A0If you're looking for some form of replication (i.e. master=
-to-slave) look at Slony - it fires triggers on the master that insert data=
into the slave. =A0It has its limitations but AFAIK it's a workable so=
lution.<div class=3D"im">
</div></blockquote><div class=3D"im"><br>No, I was not looking for a replic=
ation system. Just a "simple" method to backup and restore data f=
or users with very very low knowledge about computers administration and ze=
ro knowledge about postgreSQL. I'll not be able to spend much time with=
the users, so they should be able to make administration tasks without muc=
h supervision, and I was looking for=A0 a kind of "fire-and-forget&quo=
t; procedure Something they could do without many details to worry about.<b=
r>
=A0<br>
<blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, =
204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br>
- Yes, the restore process generates errors, because it tries to re-generat=
e data structures that exist in the database server<br>
at that moment. Even if I delete my own databases, some errors will appear =
(because I cannot delete the internal stuff of<br>
of the server -> the 'postgres' database for example) . Those er=
rors, could be ignored in most cases I think, but perhaps create a kind of =
"bad feeling" about the result of the restore process, =A0or can =
"hide" other more important errors when you get a huge ammount of=
info on the screen either.<br>
</blockquote>
<br></div><blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid=
rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
You can delete the "postgres" database - it's an empty databa=
se that's created when the server is initialised so you've got some=
thing to connect to. =A0It's safe to delete, as long as you have anothe=
r database you can connect to, but there's no real reason to unless it&=
#39;s in your restore script (e.g. from pg_dumpall.)<br>
<br>
<a href=3D"http://www.postgresql.org/docs/8.3/static/manage-ag-templatedbs.=
html" target=3D"_blank">http://www.postgresql.org/docs/8.3/static/ manage-ag=
-templatedbs.html</a><br>
<br>
Regards,<br><font color=3D"#888888">
Andy<br>
</font></blockquote></div><br>Ah, ok, I thought it was NOT possible to dele=
te postgres because I was not allowed to do it from pgAdmin.<br><br>Thanks =
again,<br><br>Regards Pablo<br><br><br><br>
--0003255584f227dcf104721a3324--
