Thread: Restore - works on one server, not on other

Hi there.
I only started using pgsql recently. I built a database to use as part of a
website.
I played with backup/restore to make sure i would be able to transfer my
database to the live server and
was able to make a backup file and restore it under a new database name. 
The only error i got was to do with a procedural language but it everything
was restored as it was on the
original version.

So i got a web host, IX their called, that supports pgsql but when i looked
at the database tools on their control panel it was a php interface with no
backup or restore options just your basic tables, sequences, functions.
So i connected to the IX database through pgAdmin 3 and tried a restore. It
restored the tables but no functions or sequences.

With the functions the restore did not like the use of $$. That i can fix by
using ordinary quotes but i would still like to know why it doesnt work in
its current form.

I couldn't figure out why it didnt like the sequences.

So i started to create a test table on the IX server and found that the sql
for the table looks different than when i create it on my local server.
I think this is where the problem with the sequences lies. If i create an id
field on my local table and use the big serial data type it creates a
sequence which appears in the sql as follows:

CREATE TABLE test
( id bigint NOT NULL DEFAULT nextval('"test_id_seq"'::regclass)
)

When i follow the same procedure on the IX server their is no refrence to
the sequence in the create table sql but if i look at the column details it
shows amongst other things:
ALTER TABLE test ALTER COLUMN id SET DEFAULT
nextval('public.test_id_seq'::text);

The differences here are the 'public' before the sequence name and the
'text' instead of 'regclass'.

I am a database developer by trade but i am new to the whole admin side of
things, including the web hosting stuff so if anyone can help me understand
the above issues it would be much appreciated.

Sorry about the long rambling post :0)

-- 
View this message in context:
http://www.nabble.com/Restore---works-on-one-server%2C-not-on-other-tf4079238.html#a11594163
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.

CaptainBalrog a écrit :
> [...]
> So i got a web host, IX their called, that supports pgsql but when i looked
> at the database tools on their control panel it was a php interface with no
> backup or restore options just your basic tables, sequences, functions.
> So i connected to the IX database through pgAdmin 3 and tried a restore. It
> restored the tables but no functions or sequences.
> 
> With the functions the restore did not like the use of $$. That i can fix by
> using ordinary quotes but i would still like to know why it doesnt work in
> its current form.
> 

Dollar quoting is a feature added in 8.0 release. Perhaps you are using 
an older PostgreSQL release.

> [...]
> When i follow the same procedure on the IX server their is no refrence to
> the sequence in the create table sql but if i look at the column details it
> shows amongst other things:
> ALTER TABLE test ALTER COLUMN id SET DEFAULT
> nextval('public.test_id_seq'::text);
> 
> The differences here are the 'public' before the sequence name and the
> 'text' instead of 'regclass'.
> 

Schemas appear in 7.3 release. Don't tell me you're trying to restore to 
a server older than 7.3 :)

> I am a database developer by trade but i am new to the whole admin side of
> things, including the web hosting stuff so if anyone can help me understand
> the above issues it would be much appreciated.
> 
> Sorry about the long rambling post :0)
> 

My guess is that your web host has a really old PostgreSQL release. Can 
you tell us which release it is ?

Regards.


-- 
Guillaume.
http://www.postgresqlfr.org
http://docs.postgresqlfr.org

you were right. They are using 7.1 so i had to recreate the database by
copying the scripts from the original and changing anything that doesnt work
in 7.1

A bit of faffing about but it didnt take too long. Still, a bit frustrating.

Do you think if i took a backup of the newly created version on the 7.1
server would i be able to restore it to a version 8 server?


Guillaume Lelarge-3 wrote:
>
> CaptainBalrog a écrit :
>> [...]
>> So i got a web host, IX their called, that supports pgsql but when i
>> looked
>> at the database tools on their control panel it was a php interface with
>> no
>> backup or restore options just your basic tables, sequences, functions.
>> So i connected to the IX database through pgAdmin 3 and tried a restore.
>> It
>> restored the tables but no functions or sequences.
>>
>> With the functions the restore did not like the use of $$. That i can fix
>> by
>> using ordinary quotes but i would still like to know why it doesnt work
>> in
>> its current form.
>>
>
> Dollar quoting is a feature added in 8.0 release. Perhaps you are using
> an older PostgreSQL release.
>
>> [...]
>> When i follow the same procedure on the IX server their is no refrence to
>> the sequence in the create table sql but if i look at the column details
>> it
>> shows amongst other things:
>> ALTER TABLE test ALTER COLUMN id SET DEFAULT
>> nextval('public.test_id_seq'::text);
>>
>> The differences here are the 'public' before the sequence name and the
>> 'text' instead of 'regclass'.
>>
>
> Schemas appear in 7.3 release. Don't tell me you're trying to restore to
> a server older than 7.3 :)
>
>> I am a database developer by trade but i am new to the whole admin side
>> of
>> things, including the web hosting stuff so if anyone can help me
>> understand
>> the above issues it would be much appreciated.
>>
>> Sorry about the long rambling post :0)
>>
>
> My guess is that your web host has a really old PostgreSQL release. Can
> you tell us which release it is ?
>
> Regards.
>
>
> --
> Guillaume.
> http://www.postgresqlfr.org
> http://docs.postgresqlfr.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>
>

--
View this message in context:
http://www.nabble.com/Restore---works-on-one-server%2C-not-on-other-tf4079238.html#a11669822
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.