Tag Info

Hot answers tagged

12

One of the silent killers of MySQL Connections is the MySQL Packet. Even the I/O Thread of MySQL Replication can be victimized by this. According to the MySQL Documentation You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something ...


10

From the CREATE ROLE documentation: Note that roles are defined at the database cluster level, and so are valid in all databases in the cluster. Since pg_dump dumps a single database, you can't extract roles with that utility. The pg_dumpall --roles-only command you proposed will do the work - however you may need to filter its output so that only ...


6

This should do it for you: mysqldump -h... -u... -p... -n -d -t --routines --triggers --all-databases > MySQLStoredProc.sql -n, --no-create-db Suppress the CREATE DATABASE ... IF EXISTS statement that normally is output for each dumped database if --all-databases or --databases is given. -d, --no-data ...


5

Learn and use an ETL tool You'll save yourself a lot of time and pain if you use ETL tools like Pentaho Kettle or Talend Studio to automate the data transfer and merging. They'll happily connect to the Pg instance and the Oracle instance and do the required copying and merging using quite flexible strategies. They can consume CSV dumps too. Use CSV dumps ...


4

dezso was right, the PGPASSWORD environment variable is present when executing Tomcat from a single shell, but not when it's started as a service. The solution is to put the value in the process environment: ProcessBuilder builder = new ProcessBuilder( "C:/postgres9/bin/pg_dump.exe", "-U", "myuser", "-h" , "localhost", "mydb"); ...


4

Regarding the password, you have two choices: An environment variable named PGPASSWORD: http://www.postgresql.org/docs/current/static/libpq-envars.html A configuration file containing the password: http://www.postgresql.org/docs/current/static/libpq-pgpass.html I don't understand why you need to run the pg_dump remotely? You can run pg_dump on any ...


4

I dumped my stored procedures with the following C:\>mysqldump -u... -p... -n -d --routines --triggers --all-databases > Z:\stuff.sql Here is one the procedures with the DROP PROCEDURE included: -- -- Dumping routines for database 'lovesh' -- /*!50003 DROP PROCEDURE IF EXISTS `LoadMyData` */; /*!50003 SET @saved_cs_client = ...


4

The postgres database is just the default database that is created during initdb. It has no special meaning. If you created a different one where all your data is kept and you never created tables in the postgres database, then there is no need to dump it. I don't know what pg_upgradecluster is. The default tool to upgrade the data directory (apart from ...


3

Recently I had a similar problem … Here goes all the required steps with comments … ! [On Source] ============================================================================ 1) Checks on Source a. Run (on solaris prompt) On source machine Execute: uname -a In my case the output was: SunOS clusterz1 5.10 Generic_142900-03 ...


3

version, OS, tool are helping things to help you solve this. I guess OS is Windows. version 9i. export full. If this is the case: connect to the server, set ORACLE_HOME=/where/is/oracle/home/ set PATH=%ORACLE_HOME%/bin;%PATH% set ORACLE_SID=ORCL -> or your ORACLE_SID name imp file=x.dmp logfile=imp_x.log full=y imp help=y gives some clues about the ...


3

Ok ! Let me describe how this issue was solved: 1) There was definitely a corruption issue caused by a problem with vmware tools on solaris 10. When the network interface had high transfer/load operations (sample: copy of a 2 GB DB ....), it just stoped working, in the middle of the operation. To put the interface working again, I had to ...


3

You should really increase max_locks_per_transaction. As specified in http://www.postgresql.org/docs/9.0/static/runtime-config-locks.html , changing this parameter may require to also change the System V shared memory. You have to increase that value also. In OS X this can be done as explained in ...


3

If you are using the old imp method the users and tablespaces must be created first. This includes grants, database links and any advanced queues you may be using. A sample script could look like this in Windows imp a_DBA_user/apassword LOG =C:\dump\logs\import.log file =user01.dmp fromuser=user_name touser=user_name The newer datapump method works ...


3

Ok here's what I did: I was in the ORANT\BIN folder. I had to figure out what the SID was for the DB. Did that via an Oracle client which I tried to connect remotely to Oracle on the server. set oracle_sid=a exp73.exe System/manager file=myexp13xxxx.dmp full=y log=mylog130527.log


3

The main indicator is the exit status of the pg_dump command. If it's non-zero, then something went wrong otherwise it worked all along. This is the implicit contract between any command and the shell, and breaching it would be a bug. And it's also testable when the sub-command could't be launched at all. Here's a basic skeleton in Unix shell that tests the ...


3

The temporary files are a side effect of the --format=tar option. A backup in default plain format goes through without temporary tables in the local drive. Omit the option to produce a backup in plain format without temporary local files.


3

how to create "empty" or "blank" databases I would use pg_dump with the --schema-only option to create the dump for an empty database: pg_dump mydb -s > /mypath/myfile.sql Using Linux, you could also pipe the output to psql and create a new, empty database in the same (or any other) database cluster right away. Example for the same cluster: ...


3

Restoring a dump file, fundamentally, involves inserting all of the rows and building all of the indexes for all of the data. In that light, it's not particularly relevant how the dump file is generated, whether you use one or several files, or which of the available options you select (though some of them, like extended-insert could possibly make it worse ...


2

You cannot just "open" a database backup. You have to restore the database. For this, you first need to install PostgreSQL. The latest version should be capable of taking backups from older versions, too. But older version may have problems with newer features in the backup. So your version should be at least as recent as the source. How you install ...


2

As @a_horse_with_no_name says, it's a file created with the original export utility, i.e an exp command rather than expdp. You'll need to import it with the matching original import utility, using the imp command.


2

If you're ok with writing some code: OUTPUT TO VALUE("filename.d"). FOR EACH table-name: EXPORT DELIMITER "|" table-name. END. OUTPUT CLOSE. It's possible to write something that's more automated than this if you're interested.


2

How long does this run before timing out? First step would bet to check the wait_timeout and interactive_timeout settings to make sure they're large enough for your import: SHOW VARIABLES LIKE '%_timeout'; SET SESSION wait_timeout=28800; The default is 8 hours (28800), so that's potentially not the issue. Other indications of this issue can be found here. ...


2

Those who have not had success with the other suggestions might consider taking a look at the BigDump PHP staggered MySQL dump importer script. It's a workaround for importing large database dumps into MySQL. I have used it successfully to import a large MySQL dump into my local development environment (I am using MAMP in this case).


2

It may not be the "correct" thing to do, but it might work (get 'er done, right?): Try breaking your large dump into multiple files and running them one at a time in sequence. My approach would be to break it in half and test. Then break each half in half, re-test, and so on. I'm somewhat curious if the amount of RAM you've got on your box could have ...


2

Yeah usually playing with wait_timeout and max_allowed_packets allows me to get around the error message as well.


2

I am not aware of any tools to do exactly what you are asking right now. I want to suggest a few options you might have overlooked however that may meet your needs. Database Cloning If your database is not being used 24/7 you can clone it for test cases. This would be done using during times when the db is not in use: createdb -U postgres new_dbname -T ...


2

"is there any possibility to use another way in converting oracle dump file?" Import the dump file into Oracle. Transfer the data from Oracle to SQL Server using any number of tools (flat files, SSIS, linked servers, replication) Forget about processing a dump file. The only thing that can process a dump file is Oracle. Or you can spend a year reverse ...


2

To respond to your comment, all is the default value of content keyword, so you can ignore this one. Anyway, I have done some tests. I don't know how is structured your old database, but from your question I note that you want to import only two tablespaces (TAB_ANC, IDX_ANC) and with all object/schemas within them. If you want to do this, you can perform ...


2

It sounds like you're trying to refresh a database that's a snapshot of another DB elsewhere, and do so without interrupting read access to the first DB. If so, the easiest way is actually to do it live in the snapshot DB. Just begin a transaction, truncate all the tables, and populate them with new data, then commit. Concurrent transactions will see the ...


2

You might try mydumper which is multi-threaded whereas mysqldump is single-threaded. Percona have also improved the less locking feature in the most recent version. The excellent Mysql Performance Blog has this useful article.



Only top voted, non community-wiki answers of a minimum length are eligible