I've been stuck today and run the SQL script file generated by powerdesign with the pg_restore command. . .
The process was terrible. I always thought it was a coding problem, so I modified the encoding of the serve and client, and the result. . .
Record this error:
PostgreSQL runs sql script file:
psql -d dbname -U username -f (where the script is located).sql
postgreSQL's pg_restore command
usage:
pg_restore [Options]… [file name]
General Options:
-d, --dbname=name 连接数据库name -f, --file=file name 输出file name -F, --format=c|d|t Backup file format(It should be done automatically) -l, --list Print archive files TOC Overview -v, --verbose Detailed mode -V, --version Output version information, Then exit -?, --help Show this help, Then exit
Recovery control options:
-a, --data-only Recover only data, Not included -c, --clean Before recreating，Clear first（delete）Database Objects -C, --create Create a target database -e, --exit-on-error An error occurred quit, Default is to continue -I, --index=NAME Restore the index of the specified name -j, --jobs=NUM Perform multiple parallel tasks for recovery -L, --use-list=FILENAME Sort from this file using the specified content table
Output
-n, --schema=NAME Only objects are restored in this mode -N, --exclude-schema=NAME Objects in this mode are not restored -O, --no-owner Do not restore the object's belonging -P, --function=NAME(args) Restore the function with the specified name -s, --schema-only Recover mode only, Excluding data -S, --superuser=NAME Use the specified superuser to disable the trigger -t, --table=NAME Restoring naming relationships（surface、Views, etc.） -T, --trigger=NAME Restore triggers with specified names -x, --no-privileges Skip the recovery of processing permissions (grant/revoke) -1, --single-transaction Recover as a single transaction –disable-triggers Disable triggers while recovering data only –enable-row-security Enable row security –if-exists Used when deleting an objectIF EXISTS –no-comments No comments are restored –no-data-for-failed-tables 对那些无法创建的surface不进行
Data recovery
–no-publications No resumption of issuance –no-security-labels No security tag information is restored –no-subscriptions Don't restore subscription –no-tablespaces The allocation information of the tablespace is not restored –section=SECTION Restoring Named Festival (Before the data、Data and data after) –strict-names Requires each table and(or)schemaInclude patterns to match at least one entity –use-set-session-authorization use SESSION AUTHORIZATION Command instead ALTER OWNER Command to set ownership
Join options:
-h, --host=Host Name 数据库服务器的Host Name或套接字目录 -p, --port=Port number 数据库服务器的Port number -U, --username=name Join as specified database user -w, --no-password Never prompt for password -W, --password Force password prompt (automatic) –role=ROLENAME Execute before recoverySET ROLEoperate
Options -I, -n, -N, -P, -t, -T, and --section can be used in combination and specified
Used to select multiple objects multiple times.
I hope I don't make this low-level mistake like me
By the way, check the encoding and modification of server and client
Find the file and modify the value of parameter lc_messages to UTF8
Restart PostgreSQL's service.
----View the server character set:
test=> show server_encoding;
—View the client character set:
test=> show client_encoding;
Supplement: Usage of pg_restore and psql recovery data
1. Use psql to restore data backup in SQL text format (i.e. a text file containing SQL scripts)
Recover an SQL backup file and ignore all errors that may occur during the process:
psql -U postgres -f
Restore an SQL backup file, and stop recovering immediately if any errors occur:
psql -U postgres --set ON_ERROR_STOP=ON -f
Restore data from SQL text to a specified database:
psql -U postgres -d mydb -f select_objects.sql
2. Use pg_restore to restore
Function:
(1) Support parallel recovery, and use the -j option to control the number of threads to recover in parallel. Multiple recovery threads can be processed in parallel, each thread processes one table. This mode can significantly improve recovery speed.
(2) You can use pg_restore to scan the backup file to generate a backup content list. Through this list, you can confirm what the backup red contains. You can also control what content is restored by editing this content list.
(3) pg_dump supports selectively only backup of some objects to save backup time. Similarly, pg_restore also supports selectively restore only partial objects, regardless of whether the backup file itself is a full library backup or a backup of some objects.
(4) Most of the functions of pg_restore are backward compatible, that is, they support the recovery of backup data produced by the old version of PostgreSQL to the new version of PostgreSQL.
Before performing recovery actions using pg_restore, create the target database:
create database mydb;
Then perform recovery:
pg_restore --dbname=mydb --jobs=4 --verbose
If the database used in backup and restore is the same name, you can eliminate the process of creating a separate library by adding the --create option, as follows:
pg_restore --dbname=postgres --create --jobs=4 --verbose
Note: If the --create option is specified, the restored database name will be the database name at the time of backup by default, and renames are not allowed. If the --dbname option is also specified, the database name connected at this time must not be the database name to be restored, because the database must be built before restoring the database.
Before building a database, you must first connect to an existing database. The --dbname option specifies which database to connect to before establishing the restored database, so it must not have the same name as the database to be restored. We generally specify to connect to the postgres database first.
pg_restore in version 9.2 or newer version supports the --section option. After adding this option, you can only restore the table structure without restoring the table data. The specific approach is to create the target recovery database first:
create database mydb2;
Then use pg_restore:
pg_restore --dbname=mydb2 --section=pre-data --jobs=4
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.