Hi to EveryOne,
Could Someone identify the eventual mistake?
In order to transfer all the DB that ar working on a server linux, TO a windows laptop, I:
1.) made a copy of the dir: /Postgres/8.4/data/base on Linux and;
2.) I put it in Postgresql/9.0/data on windows.
But now, on windows laptop, I do not see the source db.
Tanks in advance.
Gianni Pinna
Hi Gianni,
you can’t just take the dir and move it unrestricted from one OS to the other.
Easiest way is to backup the databases on the linux server and restore them on the windows laptop.
I found this faster anyway, because Postgres is creating a compacted backup here, instead of all the small files your copying when moving the base folder.
For the backup/restore you can easily use pgAdmin.
Hope this helps.
Hi Marc,
tank You for Your prompt reply.
I attempted to execute Your suggest, but I have problems while restoring.
Tanks in advance.
Gianni Pinna.
pg_restore: creazione di INDEX up_ui
pg_restore: [archiviatore (db)] Errore nalla voce TOC 1861; 1259 16530 INDEX up_ui pgsql
pg_restore: [archiviatore (db)] could not execute query: ERROR: relation "up_ui" already exists
Command was: CREATE INDEX up_ui ON servoy_user_properties USING btree (user_id);
pg_restore: impostazione proprietario e privilegi per TABLE servoy_client_stats
pg_restore: impostazione proprietario e privilegi per TABLE servoy_columninfo
pg_restore: impostazione proprietario e privilegi per TABLE servoy_content_spec
pg_restore: impostazione proprietario e privilegi per TABLE servoy_element_properties
pg_restore: impostazione proprietario e privilegi per TABLE servoy_elements
pg_restore: impostazione proprietario e privilegi per TABLE servoy_group_elements
pg_restore: impostazione proprietario e privilegi per TABLE servoy_groups
pg_restore: impostazione proprietario e privilegi per TABLE servoy_media
pg_restore: impostazione proprietario e privilegi per TABLE servoy_object_types
pg_restore: impostazione proprietario e privilegi per TABLE servoy_releases
pg_restore: impostazione proprietario e privilegi per TABLE servoy_revision_history
pg_restore: impostazione proprietario e privilegi per TABLE servoy_root_elements
pg_restore: impostazione proprietario e privilegi per TABLE servoy_user_groups
pg_restore: impostazione proprietario e privilegi per TABLE servoy_user_properties
pg_restore: impostazione proprietario e privilegi per TABLE servoy_users
pg_restore: impostazione proprietario e privilegi per TABLE DATA servoy_client_stats
pg_restore: impostazione proprietario e privilegi per TABLE DATA servoy_columninfo
pg_restore: impostazione proprietario e privilegi per TABLE DATA servoy_content_spec
pg_restore: impostazione proprietario e privilegi per TABLE DATA servoy_element_properties
pg_restore: impostazione proprietario e privilegi per TABLE DATA servoy_elements
pg_restore: impostazione proprietario e privilegi per TABLE DATA servoy_group_elements
pg_restore: impostazione proprietario e privilegi per TABLE DATA servoy_groups
pg_restore: impostazione proprietario e privilegi per TABLE DATA servoy_media
pg_restore: impostazione proprietario e privilegi per TABLE DATA servoy_object_types
pg_restore: impostazione proprietario e privilegi per TABLE DATA servoy_releases
pg_restore: impostazione proprietario e privilegi per TABLE DATA servoy_revision_history
pg_restore: impostazione proprietario e privilegi per TABLE DATA servoy_root_elements
pg_restore: impostazione proprietario e privilegi per TABLE DATA servoy_user_groups
pg_restore: impostazione proprietario e privilegi per TABLE DATA servoy_user_properties
pg_restore: impostazione proprietario e privilegi per TABLE DATA servoy_users
pg_restore: impostazione proprietario e privilegi per CONSTRAINT servoy_client_stats_pkey
pg_restore: impostazione proprietario e privilegi per CONSTRAINT servoy_columninfo_pkey
pg_restore: impostazione proprietario e privilegi per CONSTRAINT servoy_content_spec_pkey
pg_restore: impostazione proprietario e privilegi per CONSTRAINT servoy_element_properties_pkey
pg_restore: impostazione proprietario e privilegi per CONSTRAINT servoy_elements_pkey
pg_restore: impostazione proprietario e privilegi per CONSTRAINT servoy_group_elements_pkey
pg_restore: impostazione proprietario e privilegi per CONSTRAINT servoy_groups_pkey
pg_restore: impostazione proprietario e privilegi per CONSTRAINT servoy_media_pkey
pg_restore: impostazione proprietario e privilegi per CONSTRAINT servoy_object_types_pkey
pg_restore: impostazione proprietario e privilegi per CONSTRAINT servoy_releases_pkey
pg_restore: impostazione proprietario e privilegi per CONSTRAINT servoy_revision_history_pkey
pg_restore: impostazione proprietario e privilegi per CONSTRAINT servoy_root_elements_pkey
pg_restore: impostazione proprietario e privilegi per CONSTRAINT servoy_user_groups_pkey
pg_restore: impostazione proprietario e privilegi per CONSTRAINT servoy_user_properties_pkey
pg_restore: impostazione proprietario e privilegi per CONSTRAINT servoy_users_pkey
pg_restore: impostazione proprietario e privilegi per INDEX cs_oti
pg_restore: impostazione proprietario e privilegi per INDEX e_er
pg_restore: impostazione proprietario e privilegi per INDEX e_oti
pg_restore: impostazione proprietario e privilegi per INDEX e_pe
pg_restore: impostazione proprietario e privilegi per INDEX e_r
pg_restore: impostazione proprietario e privilegi per INDEX e_ur
pg_restore: impostazione proprietario e privilegi per INDEX ep_ci
pg_restore: impostazione proprietario e privilegi per INDEX ep_e
pg_restore: impostazione proprietario e privilegi per INDEX ep_er
pg_restore: impostazione proprietario e privilegi per INDEX ep_erc
pg_restore: impostazione proprietario e privilegi per INDEX ep_rev
pg_restore: impostazione proprietario e privilegi per INDEX g_n
pg_restore: impostazione proprietario e privilegi per INDEX ge_ri
pg_restore: impostazione proprietario e privilegi per INDEX r_rerr
pg_restore: impostazione proprietario e privilegi per INDEX re_no
pg_restore: impostazione proprietario e privilegi per INDEX re_o
pg_restore: impostazione proprietario e privilegi per INDEX rh_r
pg_restore: impostazione proprietario e privilegi per INDEX u_ui
pg_restore: impostazione proprietario e privilegi per INDEX u_un
pg_restore: impostazione proprietario e privilegi per INDEX up_ui
ATTENZIONE: nel ripristino è stato ignorato l'errore: 79
Il processo ha ritornato il codice di uscita 1.
Hi Gianni,
My Italian is not so good, lucky for me there’s 1 line of english…
For some reason some objects seem to exist in your database.
Did you clean up the base folder before attempting the restore?
Hi Marc,
Tank You again for Your intelligent, prompt an Kind help.
In order to remove all problem (ownwer …and so on) I am wondering wether the best way is :
pg_dumpall
to execute from Pgadmin.
In this way with only one command we have an exact copy of all Db and DB properties.
Wath do You think about, Marc?
Tanks in advance
Gianni Pinna
Hi Gianni:
Are your sure that both databases have the same user and privileges?
According to the log, seem to be the problem.
Best regards.
cuoredisardegna@tiscali.i:
pg_dumpall
to execute from Pgadmin.
In this way with only one command we have an exact copy of all Db and DB properties.
Wath do You think about, Marc?
In pgAdmin you can rightclick on a database and select either backup or restore.
That’s definitely the easiest way without having to type the correct command.
But as Adelo mentioned how about user/privileges?
Hi Gianni,
You can’t just copy the base directory from the data directory. If you want to move the files over you should copy the whole data directory over (when PostgreSQL is not running).
And like Marc already pointed out you can’t do this in all situations. For instance you can’t move it from a 32 bit install to a 64-bit install (or vice versa).
Doing a dump/dumpall is then the only way to go.
But since you already moved the base directory to your windows data directory this means your cluster is now messed up. You should stop PostgreSQL, delete this data directory and reinitialize this cluster by using the initdb command. Then start PostgreSQL again and create new users.
After you have done this you can start importing the dumpfile.
Perhaps it’s even easier to just uninstall PostgreSQL, delete the data directory (the uninstaller won’t remove it) and reinstall PostgreSQL again because this will also create users and such for you.
After this you can import your dumpfile.
Hope this helps.
Hi Adel,
Hi Marc,
Hi Robert,
many Tanks to Each of Your Person.
I have to experiment Your intelligent suggests.
If I will encounter any execution difficulty, I hope to can turn to the science of Yours.
An grateful and humble thought of esteem to Each of You.
Sincerly.
Gianni Pinna.
Hi Robert,
I have executed theese specifications of Your:
Perhaps it’s even easier to
just uninstall PostgreSQL,
delete the data directory (the uninstaller won’t remove it) and
reinstall PostgreSQL again because this will also create users and such for you.
Now is nedded to execute this one:
After this you can import your dumpfile.
.
But it is possible to observe thath if You wish to backup server (with all databases -it seems-) from pgAdmin it is possible to be done. In contray if You wish to restore the server there is no one command in pgAdmin.
Sure You know the correct way.
Tanks in advance.
Gianni Pinna
Hi Gianni,
You can backup the whole database cluster (all databases AND roles/groups, etc…) by right-clicking on the connected server node (see screenshot).
This essentially runs the commandline tool ‘pg_dumpall’ for you.
[attachment=0]backup whole cluster.png[/attachment]
This will create a plain text SQL file that you then can ‘run’ against the other PostgreSQL instance.
But since this is a rather large file you can’t really use PgAdmin’s Query editor because that wants to load the file completely into the editor before you can execute it.
So using the commandline tool psql would be the preferred method to restore this backup.
psql -d postgres -u postgres -f /path/to/your/backupfile.sql
This will connect to the ‘postgres’ database (-d) using the role ‘postgres’ (-u) and executes the sql file (-f)
Adjust the parameters to match your setup.
Hope this helps.
Hi Robert,
Your answer is luminous as far as the last detail.
“Fortissima est veritas” -The truth is most strong. It causes that the Person who possesses it becames good. But the Person becames more god when She offers the truth that he has. Both in the pratical and in the speculative sciences.
A mild grateful and humble tank to You and again to Adel and Marc.
Gianni Pinna.
