Prepare for Project release (Freesbsd on Postgres upgrades, etc.)

0. Remove unnecessary tables and stored procedures from the 200 server Setup database

DROP TABLE "Sensorpressure";
DROP FUNCTION hex_to_int (text);
DROP FUNCTION "Pressuredatatosensorpressure" ();

1. Back up 200 of the SETUPD database,:p g_dump-d-H localhost-u pgsql mydb (database name) > Mydb.bak

2. Reduction of setupdb to 172 graindb:p sql-h localhost-u pgsql-d graindb

(encountered a problem: prompt-D option is invalid swap with another command to back up a large database, view the Postgres document

4. Large database; 4.1 output large database; Because PostgreSQL allows the table to be larger than the maximum file size allowed by your system, it may be problematic to dump the table to a file because the resulting file is likely to be larger than the maximum file that your system allows. Because pg_dump output to standard output, you can bypass the problem with standard Unix tools: Use a compressed dump. Use your familiar compression program, such as Gzip. xiaop@xiaop-laptop:~$ pg_dump mydb (database name) | gzip > mydbback.gz 4.2 restore large database; Restore using the following command: xiaop@xiaop-laptop:~$ createdb mydbnew (New database name) xiaop@xiaop-laptop:~$ gunzip-c mydbback.gz | Psql mydbnew or xiaop@xiaop-laptop:~$ cat mydbback.gz | Gunzip | Psql mydbnew

）

Restore Postgres under Windows

D:/program files/postgresql/8.3/bin>psql-u postgres-d setupdb < G:/setup_bak

3. Check websetup triggers, stored procedures, creating code

4. Initialize Graindb:paster Setup-app using Websetup Development.ini
4.1 Manually invoke stored procedures to perform Pressuredata table to sensorpressure table conversions

5. Average and fitting procedures for hand-run timing tasks

6. Use 172 graindb as a database run a Web server quick test full functionality

7. Modify 210 Server GRAINRPC database server address and database name is 172 database

8. Check again if the Web server is getting the latest data

9. Run a timed task to make it a background program

10. Delete FreeBSD on postgres8.3 database, install 8.4 from ports

(encountered a point problem, reference documentation: HTTP://WWW.CHEEREN.COM/NODE/18)

Alas. Upgrade ... Upgrade ... Upgrade ... This lineup has been upgraded, this round to the PostgreSQL big upgrade. Upgrade... Upgrade... Upgrade... This has been upgraded for a while, this turn to PostgreSQL big upgrade. PostgreSQL Unlike other software, even if it is 8.2 liters to 8.3, it must be changed greatly, so can not directly with the upgrade tool directly ascend. PostgreSQL Unlike other software, even if the 8.2 upgrade to 8.3, it must be changed, so you can not directly with the upgrade tool directly liters. Even the Windows version must be labor-free. Even the Windows version must be a chore. This time I use the FreeBSD version of the upgrade as an example, to explain the simplest and the most correct major revision steps. This time I use the FreeBSD version of the upgrade as an example, to illustrate the simplest and most correct upgrade step of the big revision. First of all, please put the original data of the library completely ready. First of all, please put the original database data completely back down. If you are a user of Windows or a user of Linux, you can consider using the "backup" or "Pgadmin" in the "Tools" menu of the "III", which is a lot simpler, but since Pgadmin III is too little, I can't understand it. The meaning of the options in the conversation cube, so it's not necessary. If you are a user of Windows or a user of Linux, you may want to consider using the "backup" or "Backup whole domain" in the "Tools" menu of Pgadmin III, which is much simpler, but because Pgadmin III has so little to say, I really don't understand. Backup dialog box, so simply do not use the meaning of the item. The most formal approach is to use the tools under the console: Pg_dump and Pg_dumpall. The most formal approach is to use the tools under the console: Pg_dump and Pg_dumpall. The difference between the two is that the former can be prepared for a particular database, and the latter generally use the entire server data to complete the backup. The difference is that the former can be backed up for a particular database, which is typically used to make a full backup of the entire server data. I recommend that direct use of pg_dumpall is more convenient and faster. I suggest that direct use of pg_dumpall is more convenient and faster. The method is as follows: $ pg_dumpall > FileName $ pg_dumpall > filename and filename You can take it yourself, like host_a.20080206.full.dumP's. and filename You can take it yourself, such as host_a.20080206.full.dump and so on. The date of inclusion in the name will give you a clearer idea of the date of this backup. Adding a date to the name will give you a clearer idea of the date the backup file was backed up. By adding the full word, you can remind yourself that this is a complete backup, not a part of the continuity of the general maintenance. By adding the full word, you can remind yourself that this is a complete backup, not a partial backup of the continuity part of the general maintenance. If you are managing more than one PostgreSQL, it is best to include the name of the library's host computer. If you are managing more than one PostgreSQL, it is best to specify the database host name. Yes, I forgot something very important. By the right, I almost forgot something very important. On Windows, as long as you use any one of the users in the Administrators group to log in, and then →postgresql 8.3→command Prompt from the Start menu, you can open the command prompt character, and you can go to Pg_dumpall directive; However, in FreeBSD, the general installation, the preset root is not allowed to perform PostgreSQL related instructions, so please switch to Pgsql user, if you do not more mobile pgsql password, then the preset password is " Random ", that is, you can not guess how to guess, but you could use SU cut to root, and then use Su-pgsql cut into pgsql users, do not enter any password can, this time can execute the above instructions. On Windows, as long as you use any one of the users in the Administrators group login, and then from the Start menu →postgresql 8.3→command Prompt, you can open the command prompt character, and can be issued Pg_dumpall directive; However, in FreeBSD, a general installation, preset root is not allowed to execute PostgreSQL related instructions, so please switch to pgsql users, if you do not change the pgsql password, then the default password is very " Random, that is, you can not guess how to guess, but you may first cut to root with Su, and then use Su-pgsql switch to pgsql users, do not enter any password, then you can execute the above instructions. Note, however, that it is best to move this backup to a safe location so that it is not 傎 to continue the installation process. Note, however, that it is best to move this backup file to a safe location to avoid 傎 during the installation process. So you'd better jump back to root first., please go to the Exit command, and then use the CP command to copy the dump file to any location you have temporarily saved. So at this point you'd better jump back to root, go to the Exit command, and then use the CP directive to copy the dump file to any location you're staging. Then the formal installation was performed. The installation is then formally performed. Before installing a new version, it is necessary to remove the old version before the two daemon will be robbed of the same TCP port, and there is an unpredictable situation. Before installing a new version, you must first remove the old version, or two daemon will rob the same TCP port, and there will be unpredictable conditions when you turn on the boot. First look at your server, what is currently installed and PostgreSQL package: First look at your server, what is currently installed and PostgreSQL related package: # Pkg_info | grep PostgreSQL # Pkg_info | grep PostgreSQL At this time you can at least see two package out of the list, and I have one more: At this point you can see at least two package appear in the listing, and I have one more: Postgresql-client-8.2.6_1 PostgreSQL Database (client) Postgresql-client-8.2.6_1 PostgreSQL database (client) postgresql-contrib-8.2.6 the Contrib utilities from the PostgreSQL distribution postgresql-contrib-8.2.6 the contrib utilities from the PostgreSQL Ribution postgresql-server-8.2.6 The most advanced Open-source database available anywhere postgresql-server-8.2.6 the MO St Advanced Open-source database available anywhere in general, if you do not have an Install server instrumentation function, then only the client and server two package. In general, if you do not have the server instrumentation feature installed, thenThere should be only client and server two package. But this little piece is not complete, and if you still have the drivers for PHP and PostgreSQL, then this is not going to happen in this checklist. But this small list is not complete, and if you still have PHP and PostgreSQL-related drivers, then this is not the list. Do not worry, if you remove the order is not right, a dependent package, then remove the dependency suite, and then remove the target kit. Do not worry, if you remove the wrong order, there are dependent packages, then remove the dependency suite, and then remove the target suite. Just this time you'd better take a note of which packages you have removed altogether, and then you will be able to reinstall them again in one by one. It's just this time you'd better take a note of what packages you've removed altogether, and then you'll be able to reinstall them again in the future one by one. Let's start with these three package, and if you don't remove the old version of the server, the immediate removal of the client will be alerted and the installation will stop. Let's start with these three package, and if you don't remove the old version of the server, the immediate removal of the client is alerted and the installation action stops. Similarly, if you do not first remove the old version of contrib and PHP package, you will be stopped from removing the client when you remove the client because the dependency suite has not been removed. Similarly, if you have not removed the previous version of contrib and PHP-related package, you will be stopped from removing the client when you remove the client because the dependency suite has not been removed. So if you take the above three as an example, the order of removal is: So if you take the above three as an example, the sequence of removal is: #/usr/local/etc/rc.d/postgresql stop <--First stop the current PostgreSQL server #/usr/ Local/etc/rc.d/postgresql Stop <--First stop the current PostgreSQL server # pkg_delete postgresql-contrib-8.2.6 # Pkg_delete postgresql-contrib-8.2.6 # pkg_delete postgresql-server-8.2.6 # pkg_delete postgresql-server-8.2.6 # Pkg_delete Postgresql-client-8.2.6_1 # Pkg_delete PostGresql-client-8.2.6_1 this time, please remove the entire/usr/local/pgsql/data, as I just renamed it, and let it continue, saying that some of the files in the catalogue will be used someday: mv/usr/local/pgsql/ data/usr/local/pgsql/data_8.2. Then please remove the entire directory of/usr/local/pgsql/data, like I just changed it to a name, and let it stay, and maybe someday will use some of the files in the directory: mv/usr/local/pgsql/data/usr/local/pgsql/ data_8.2. Then you can start using port to install PostgreSQL 8.3. You can then start using port to install PostgreSQL 8.3. Before installing, please make sure that the port tree on your server is the latest version, if your port is updated before the dawn of 2008/2/5, may not be the latest version of the postgresql-server-8.3, please move on to update the port. Before installing, please make sure that the port tree on your server is already the latest version, if your port is updated before the dawn of 2008/2/5, may not be the latest version of the postgresql-server-8.3, please manually update the port. If it's the latest version, cut to the port: if it's the latest version, cut to the Port directory: # cd/usr/ports/databases/postgresql83-server # cd/usr/ports/databases/ Postgresql83-server # make install the clean # make install-FreeBSD will automatically help you install version 8.3 server and client. FreeBSD will automatically help you install version 8.3 of server and client together. Then the contrib package is also mounted: then the contrib package is also installed: # CD. /postgresql-contrib # CD. /postgresql-contrib # Make install clean # made install if you've ever removed PHP5 driveror JDBC, etc. before removing the kit, now also please use the same method to put all the new version one by one back. If you've ever removed php5 driver or JDBC and so forth, now please put all the new version one by one back in the same way. That's good. That's good. It's still early, please see if you've built a new library under your/usr/local/pgsql. It's still early, please see if you have built a new database under your/usr/local/pgsql. # ls/usr/local/pgsql # Ls/usr/local/pgsql If this record only shows a renamed data_8.2, then that means makefile does not automatically help you do Initdb, Please perform: If there is only one data_8.2 in this directory, then the representative makefile does not automatically help you do initdb, please carry out: #/usr/local/etc/rc.d/postgresql Initdb [1] #/usr/ Local/etc/rc.d/postgresql Initdb [1] Now that you've ls/usr/local/pgsql instructions, you can see one more data record, so that you're sure to activate PostgreSQL: And then you go down ls/usr/ Local/pgsql instructions, you can see a more than one data directory, so that the correct start PostgreSQL: #/usr/local/etc/rc.d/postgresql start [2] #/USR/LOCAL/ETC/RC.D /postgresql start [2] If you don't have any error messages, congratulations, you're installed. If there is no error message, congratulations first, install. But the job is half done, and then the original backup is put back into the repository: But the work is half done, and the original backed-up data is saved back to the database: # su-pgsql # su-pgsql $ psql-f filename Postgres $ psql-f FileName Postgres is the first to cut into the Pgsql user, followed by the PostgreSQL Interactive instruction input tool: Psql, and then the name of your dump file (this is the full path name) instead of the above filename. That is, first cut into the Pgsql user, followed by the implementation of PostgreSQL Interactive instruction input tool: Psql, and then put justThe name of your dump file (which is, of course, the full path name) replaces the filename above. Then some information is being done on behalf of what the repository is doing. Then some messages represent the movement of the database back to the store and what is being done. If all the wrong messages are not present, congratulations, the job is done 80%. If all the wrong messages have not appeared, then congratulations, the work is done 80%. For Windows users, as long as you remember the last instruction, you can save the data that you just pg_dumpall back. For Windows users, just remember the last instruction, you can save the data that you just pg_dumpall back. Windows users are really happy. Windows users are really happy. What. What the. Only to complete the 80%. Only to complete the 80%. Yes, there's two of them. You have more to do with the previous version of Postgresql.conf and pg_hba.conf, they should be in the FreeBSD in the/usr/local/pgsql/data, please take the previous change back, But please read the release notes first, because many of the management items in the postgresql.conf have been more active, you may have previously changed the project, version 8.3 has merged the project and other items, and took a new project name, first read release notes e.1.2.2. Configuration Parameters is very important. Yes, there are two files possible before the version you have changed, respectively, postgresql.conf and pg_hba.conf, they should be located in the FreeBSD in the/usr/local/pgsql/data directory, please change the previous changes back, But please read the release notes first, because many of the management items in the postgresql.conf have been changed, and you may have previously modified the project, version 8.3 has merged the item and other items, and took the new project name, read release notes e.1.2.2. Configuration Parameters is very important. Also, if you just delete the old version of the 8.2/usr/local/pgsql/data catalogue, it's embarrassing at the moment, you have to use your memory to think back to the items and content that you've been doing more, so I'll recommend just renaming it and keeping it. Also, if the old 8.2 version of the/usr/local/pgsql/data directory directly deleted, that is now embarrassing, you have to use yourMemory recalls the items and content that you have changed, so I would suggest just renaming it and keeping it. If you are interested in PostgreSQL's backup and return, then the English program is OK, so please step into the PostgreSQL line and say: Chapter 24. Backup and Restore. If you are interested in PostgreSQL backup and return, English program also can, then please refer to PostgreSQL's online instructions: Chapter 24. Backup and Restore. Note: [1] Of course, if the new data record has been generated, that is, makefile has automatically helped you perform INITDB action, then initdb this motion can be saved. [1] Of course, if the new data directory has been generated, that is, makefile has automatically helped you perform INITDB action, then initdb this action can be saved. The reason I would particularly like to mention this is that I now have a two days version of Makefile that is weird, and sometimes does not automatically perform initdb. The reason I would particularly mention this is that my current two-day preemptive version of Makefile is strange and sometimes does not automatically perform INITDB actions. [2] If you don't have the hassle of pg_dumpall and psql to save back, the direct upgrade (or to remove the old version first and install the new Edition), on the surface can still be upgraded, but, in fact, Makefile will put your old version of the postgresql.conf and pg_hba.conf a copy into the new data catalogue. [2] If you don't have the hassle of using Pg_dumpall and psql to back up your backups, you can upgrade directly (or you'll have to remove the old version and install the new one), but in fact makefile will put your old version of postgresql.conf and pg_hba.conf a copy into the new data directory. As I said in the article, postgresql.conf is much more active in this version, so when you intend to activate the library, fatal ERROR will appear. As I said in the article, Postgresql.conf has a big change in this version, so when you plan to start the database, fatal error occurs. The solution is simple, with no need to recharge, remove the new Data Catalogue (renamed), perform/usr/local/etc/postgresql initdb, redo the initdb, and there will be a new version of dataUp. The solution is simple, no need to recharge, remove the new data directory (as with renamed), perform/usr/local/etc/postgresql initdb, redo the initdb, there will be a new version of data. [3] This article is only suitable for servers that are not loaded if your server is a mission critical server, although the basic upgrade concept is the same, that is, the data library is upgraded to a new installation, so the service will certainly be interrupted, but prior training and good plan, Reduce the time to stop service. [3] This article applies only to servers with little load, if your server is a mission critical server, although the basic upgrade concept is the same, that is, database upgrade equivalent to reinstall, so the service is bound to be interrupted, but prior to the drill and good planning, Can reduce downtime of service. In this case, the advice has to have to increase the backup server, at the same time as the main server upgrade, so that the backup server for the first time service. In this case, it is recommended that additional backup servers be added to the primary server to allow the standby server to serve temporarily. To say this, you have to get involved in the subject of "Data Library Replication" (replication). In this case, the topic of "Database Replication" (replication) has to be involved. When I'm free, I'll write an article about PostgreSQL and replication. When I have time to write a PostgreSQL and replication related articles.

11.172 database backup, restore to 210
11.1 Check to see if there are 8.3 bugs
12 Modify 210 Server GRAINRPC database server address and database name 210 database

0. Delete the existing setupdb.bak in the/home/ftpuser/upload
1. Access to 210 server/home/ftpuser/upload Backup Database pg_dump-d-H localhost-u pgsql setupdb > Setupdb.bak
2. Use the FTP tool to download and save to the project directory
3. Establishment of the GRAINDB database on 172
Restore to 172 server psql-h localhost-u pgsql-d graindb

Final detection data is normal